Learn about Aggregate Functions in SQL

Introduction

Functions are a very powerful feature of SQL and can be used to do the following:

• Perform calculations on data
• Modify individual data items
• Manipulate output for groups of rows
• Format dates and numbers for display

SQL functions sometimes take arguments and always return a value.

Aggregate Functions

An aggregate function in database administration is a function that groups the values of various rows as input according to specific criteria to create a single value with more profound meaning.

Some aggregate functions of SQL are as follows:

1) Count()

2) Sum()

3) Avg()

4) Min()

5) Max()

Consider the following table

Count()

Count(*): Gives total records found. i.e 6

Count(salary): Return the number of non-null values found in the salary column.i.e 5

Count(Distinct Salary): Returns the number of unique Non Null values found within the salary column, i.e. 3.

Explanation: ID 1,2,3 has a unique value of salary. Other ID value are repeated

Sum()

sum(salary): Sum all Non Null values of Column salary i.e., 330

Explanation: 80+30+70+80+70

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

Explanation: 80+30+70=180

Avg()

Avg(salary) = Sum(salary) / count(salary) = 330/5

Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 180/3

Min()

Min(salary): Minimum value in the salary column except NULL i.e., 30.

Max(salary): Maximum value in the salary i.e., 80.

Elaborated Example with queries

TABLE: PRODUCT

COUNT()

OUTPUT

COUNT() WITH GROUPBY

OUTPUT

COUNT() WITH HAVING

OUTPUT

SUM

OUTPUT

SUM WITH HAVING()

OUTPUT

AVG

OUTPUT

MAX

OUTPUT

MIN

OUTPUT

Conclusion

Aggregate functions count(),sum(),min(),max(),avg() along with syntax and examples were discussed.