Top SQL Aggregate Functions Count, Avg and Max | 2022

If you’ve ever worked with SQL databases, you’re probably familiar with these SQL aggregate functions. In this post, we’ll go over the SQL aggregate functions examples.

Commonly Used Aggregate Functions in SQL

All aggregate functions are deterministic i.e returning the same value every time whenever called with a specific set of input values.

• Count
• Sum
• Avg
• Min
• Max

Aggregate Functions in Mathematics

Mathematically an aggregate function is a computation of a range of values resulting in a single value that expresses the significance of the accumulated data that it is derived. These functions are used for the derivation of descriptive statistics.

What are the Aggregate Functions in SQL? 

There are lots of functions in SQL and aggregate functions are one of them. 

Aggregate functions are used in database management to group the values of multiple rows and return only a single scalar value by performing calculations on those sets of values.

Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement where null values are ignored (except for COUNT(*))

The syntax for Aggregate Functions in SQL

The syntax of calling an aggregate function is as follows:-

aggregate_function (DISTINCT | ALL expression)

Where the meanings are:-

1.) aggregate_function:- This specifies the name of the aggregate function to be used which can be count(), min(),sum() , etc.

2.) DISTINCT | ALL expression:- Distinct modifier is used to ignore duplicate values and only consider the unique/distinct values. | is an OR symbol. ALL modifier is used for considering all the values in the calculation.

Note:- All modifier is used by default when no modifier is defined explicitly.

3.) expression:- It can be any expression of multiple columns with arithmetic operators.

Example of SQL Aggregate Functions in SQL

Let’s understand the Aggregate Functions of SQL that we mentioned above with the help of examples.

For the demonstration, we created a table called students with the columns Id, Name, and Marks. The table has five rows, one for each student, with numbers ranging from 1 to 100.

1. COUNT FUNCTION  Example

COUNT is an SQL aggregate function that counts the total number of values(both numeric and non-numeric data types) present and returns the values according to the criteria given.

Syntax

For example, to find the total number of students who are less than 15 years of age, we can use the given query. 

SELECT COUNT(*) FROM students 

We have five rows in the table. So count functions should give use 5 as output.

Points to remember about the COUNT function

1. COUNT helps to verify results very simply.
2. COUNT(1) has the same effect as COUNT(*).
3. COUNT (*) also considers Nulls and duplicates
4. If the column name of the table is used instead of *. Unlike COUNT(*), the variation COUNT(column_name) will not count NULL values in that column.

2. How to use SUM in SQL

SUM is an SQL aggregate function used to find out the sum of all the values(only numeric data types) in the selected column, where the column name is an argument and the total sum of all the values in that column is returned as result.

Syntax

For example, The query below selects the sum of the marks column from the Marks_student dataset:

SELECT SUM(marks) FROM marks_student;

There are 6 rows in the table, one of which has a null in the marks column. The null value, however, was ignored when the sum method was used.

Points to remember about the SUM function

1. The calculation works only vertically i.e only for a column and if you want to calculate across rows then simple arithmetic can be used.
2. SUM doesn’t consider Nulls as SUM treats NULL as zero
3. SUM function works on numeric fields only.

3. How to use AVG aggregate function in SQL

AVG  is an SQL aggregate function that is used to find out the average of a selected group of values(only used on numerical columns & Null values are ignored.)

Syntax

Select Avg(Column) from Table

We can use the Avg function to find out the avg marks obtained by students?

select AVG(Marks) from students

4. Min Aggregate Function in SQL

MIN is an SQL aggregate function that returns the lowest/minimum value of all selected values(both numeric and non-numeric data types) in a specified column of a table. MIN functions ignore NULL values.

Syntax

select min(column) from table

For example, we can use the given query to find the most minor/minimum value of the marks column from the table named students.

SELECT MIN(marks) FROM subjects;

5. Max Function in SQL

MAX is an SQL aggregate function that does the opposite of the MIN function.MAX function is used to return the largest/minimum value of all selected values(both numeric and non-numeric data types) in a specified column of a table. MAX functions ignore NULL values.

Syntax

select max(column) from table

For example, we can use the given query to find the largest/maximum value of the marks column from the table named subjects.

SELECT Max(marks) FROM subjects;

How to select another column along with aggregate function.

In order to select another column along with the aggregate function, you have to use group by clause.

SQL server will throw this exception when we don’t use the group by clause along with the aggregate function.

Select the top 2 students with the highest marks.

select top 2 Name,Max(Marks) as Marks from students group by Name

Conclusion

So in this article, we understood the use of different SQL aggregate functions like Count, Sum, Mix, and Max.