Aggregate function in SQL | Board Infinity

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

COUNT() WITH GROUPBY

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

OUTPUT

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

MIN

SELECT MIN(RATE)
FROM PRODUCT;

OUTPUT

Conclusion

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