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

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

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

PRODUCT

COMPANY

QTY

RATE

COST

A

A1

2

10

20

B

A2

3

25

75

C

A3

2

30

60

D

A4

5

10

50

E

A5

2

20

40

F

A6

3

25

75

G

A7

5

30

150

H

A8

3

10

30

I

A9

2

25

50

J

A10

4

30

120

COUNT()

SELECT COUNT(*) 
FROM PRODUCT;  

OUTPUT

10

COUNT() WITH GROUPBY

SELECT COMPANY, COUNT(*) 
FROM PRODUCT
GROUP BY COMPANY; 

OUTPUT

7

COUNT() WITH HAVING

SELECT COMPANY, COUNT(*) 
FROM PRODUCT
GROUP BY COMPANY 
HAVING COUNT(*)>2;  

OUTPUT

A1    5
A2    3

SUM

SELECT SUM(COST) 
FROM PRODUCT;  

OUTPUT

670

SUM WITH HAVING()

SELECT COMPANY, SUM(COST) 
FROM PRODUCT
GROUP BY COMPANY 
HAVING SUM(COST)>=160

OUTPUT

A1    335
A3    170

AVG

SELECT AVG(COST) 
FROM PRODUCT;  

OUTPUT

67.00

MAX

SELECT MAX(RATE) 
FROM PRODUCT;  

OUTPUT

30

MIN

SELECT MIN(RATE) 
FROM PRODUCT; 

OUTPUT

10

Conclusion

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