Which aggregate function would be used to find the highest value in a column?

In SQL Server, the MAX() function is an aggregate function that returns the maximum value in the column or expression.

It is an aggregate function so it canbe used with the GROUP BY clause to find the maximum value in a group.

MAX(expression)

Parameters

expression: It can be a constant, a table column, or function, and any combination of arithmetic, or string operators.

MAX can be used with numeric, character, uniqueidentifier, or with datetime data.

Return Value

Returns the maximum value in the group of the same data type. It ignores the NULL values.

Get Maximum Value in the Column

The following query fetches the maximum Salary from the Employee table.

SELECT MAX(Salary) AS MaxSal FROM Employee;

To check the above result, use the following query to fetch all records from the Employee table ordered by Salary in ascending order.

SELECT * FROM Employee ORDER BY Salary;

As you can see, the maximum Salary in the Employee is 200000.

MAX() with String Column

The MAX() function can be used on the string column. For example, the following uses the MAX() function on the LastName column of the Employee table. It will sort the column alphabetically and the last value will be returned.

SELECT MAX(LastName) FROM Employee;

Above, 'Troy' is the last value alphabetically and so it will be returned.

MAX() with GROUP BY Clause

In the following example, the maximum salary for each department is returned by using the GROUP BY clause. Here the GROUP BY clause groups the employees based on their department and then the maximum salary in each group is returned.

SELECT DepartmentId, MAX(Salary) AS DeptMaxSalary
        FROM Employee
        GROUP BY DepartmentId;

MAX() with HAVING Clause

The MAX() function can be used with the HAVING clause as shown in the below example. Here the GROUP BY clause groups the employees according to their departments, gets the maximum salary of each department, and then the HAVING clause filters the result by returning only those departments having a maximum salary greater than 50000.

SELECT DepartmentId, Max(Salary) DeptMaxSal FROM Employee
        GROUP BY DepartmentID
        HAVING MAX (Salary) > 50000;

Get Maximum and Minimum Value

In the following example, the MIN() and MAX() functions are used on the Salary column of the Employee table. The minimum and maximum salary in the table is returned.

SELECT MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employee;

In database management systems, an aggregate function is a function that performs calculations on a group of values and returns a single value.

It is used to summarize the data. It performs calculations on multiple rows of a single column of a table to form a single value of more significant meaning.

These Aggregate functions are inbuilt SQL functions. They are often used with the GROUP BY clause to calculate an aggregate value for each group.

Some of MySQL Aggregate functions are:

• AVG
• COUNT
• MAX
• MIN
• SUM

The AGGREGATE Functions ignore all NULL Values.

AVG

The SQL AVG function calculates the average value of a column of numeric type. It returns the average of all non-NULL values.

Syntax

AVG ( [ALL | DISTINCT] expression )
[ALL | DISTINCT] is optional. ALL is used to select all the records for input while DISTINCT is used to select only uniques values of the record.
expression: it specifies the input source. it may be a field of the table or a subquery.

For example:

SELECT AVG(Salary) FROM Employees;
This query returns the average salary of all the employees.


SELECT Dept_Id,AVG(Salary) FROM Employees GROUP BY Dept_Id;
This query will return the Dept_Id and Average Salary of Employees of corresponding Department.


SELECT Emp_Id, Emp_Name FROM Employees WHERE Salary > AVG(Salary);
This query will return the Emp_Id and Emp_Name of Employees whose Salary is greater the Average Salary of all the Employees.

COUNT

The COUNT function is used to count the number of rows in a database table. It can work on both numeric and non-numeric data types.

The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets on the number of rows or non-NULL column values.

COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table.

COUNT(*) considers duplicate and Null while Count(column_name) removes Null values records from counting.

Syntax

COUNT(*)
or
COUNT( [ALL | DISTINCT] expression )
[ALL | DISTINCT] is optional. By default it is ALL. DISTINCT keyword removes duplicate and Null values from operation.

For example:

SELECT COUNT(*) FROM Employees;
This query will return the total number of records in the Employees table.


SELECT COUNT(DISTINCT Dept_Id) FROM Employees;
This query will return the count total number of Dept_Id. It does not consider duplicate Dept_Id and Null.


SELECT Dept_Id, COUNT(*) FROM Employees GROUP BY Dept_Id;
This query will return the count of Employees in each department.

MAX

The aggregate function MAX() is used to find the maximum value or highest value of a certain column or expression or a set of values.

It is applicable to all the data types.

It excludes NULL values and return distinct value as a result.

Syntax

MAX(expression)

For example:

SELECT MAX(Salary) FROM Employees;
This query will return maximum salary from the Employees table.


SELECT Dept_Id, MAX(Salary) FROM Employees GROUP BY Dept_Id;
This query will return the Dept_Id along with the maximum salary of each department.


SELECT * FROM Employees WHERE Salary = MAX(Salary);
This query will return all the details of the Employee who has the maximum salary.


SELECT * FROM Employees WHERE Hire_Date = MAX(Hire_Date);
This query will return the details of the Employee who has been in the Company for the least amount of time.

MIN

The aggregate function MIN() is used to find the minimum value or lowest value of a certain column or expression or a set of values.

It is applicable to all the data types.

It excludes NULL values and return distinct value as a result.

Syntax

MIN(expression)

For example

SELECT MIN(Salary) FROM Employees;
This query will return minimum salary from the Employees table.


SELECT Dept_Id, MIN(Salary) FROM Employees GROUP BY Dept_Id;
This query will return the Dept_Id along with the minimum salary of each department.


SELECT * FROM Employees WHERE Salary = MIN(Salary);
This query will return all the details of the Employee who has the minimum salary.


SELECT * FROM Employees WHERE Hire_Date = MIN(Hire_Date);
This query will return the details of the Employee who has been at the Company for the longest time.

SUM

The aggregate function SUM() is used to calculate the sum of all the values of the select column. It returns the sum of values in a set.

SUM function ignores the NULL values. If no matching records are found, it returns NULL.

It is applicable to numeric values.

Syntax

SUM([ALL | DISTINCT]) expression)
DISTINCT is used to select unique values.

For example:

SELECT SUM(Salary) FROM Employees;
This query will return total salary of all the Employees.


SELECT Dept_Id, SUM(Salary) FROM Employees GROUP BY Dept_Id;
This query will return the Dept_Id along with the total salary of all the employees in each department.


SELECT Dept_Id FROM Employees WHERE SUM(Salary) > 200000;
This query will return the Dept_Id in which the total salary of all the employees in the department is greater than 200000.

That's all about aggregate functions. Hope you learned something new today.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here.

Keep Learning :)

Team AfterAcademy!