Aggregate functions in SQL

In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.

Various Aggregate Functions

1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()

Now let us understand each Aggregate function with a example:

Id     Name     Salary
-----------------------
1       A        80
2       B        40
3       C        60
4       D        70
5       E        60
6       F        Null

Count():

Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary):  Return number of distinct Non Null values over the column salary .i.e 4

Sum():

sum(salary):  Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.

Avg():

Avg(salary) = Sum(salary) / count(salary) = 310/5
Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4

Min():

Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above

My Personal Notes arrow_drop_up

Save