SQL Aggregate Functions

• SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
• It is also used to summarize the data.

Types of SQL Aggregation Function

1. COUNT FUNCTION

• COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
• COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.

Syntax

1. COUNT(*)  
2. or  
3. COUNT( [ALL|DISTINCT] expression )  

Sample table:

PRODUCT_MAST

PRODUCT	COMPANY	QTY	RATE	COST
Item1	Com1	2	10	20
Item2	Com2	3	25	75
Item3	Com1	2	30	60
Item4	Com3	5	10	50
Item5	Com2	2	20	40
Item6	Cpm1	3	25	75
Item7	Com1	5	30	150
Item8	Com1	3	10	30
Item9	Com2	2	25	50
Item10	Com3	4	30	120

Example: COUNT()

1. SELECT COUNT(*)  
2. FROM PRODUCT_MAST;  

Output:

10

Example: COUNT with WHERE

1. SELECT COUNT(*)  
2. FROM PRODUCT_MAST;  
3. WHERE RATE>=20;  

Output:

7

Example: COUNT() with DISTINCT

1. SELECT COUNT(DISTINCT COMPANY)  
2. FROM PRODUCT_MAST;    

Output:

3

Example: COUNT() with GROUP BY

1. SELECT COMPANY, COUNT(*)  
2. FROM PRODUCT_MAST  
3. GROUP BY COMPANY;  

Output:

Com1    5
Com2    3
Com3    2

Example: COUNT() with HAVING

1. SELECT COMPANY, COUNT(*)  
2. FROM PRODUCT_MAST  
3. GROUP BY COMPANY  
4. HAVING COUNT(*)>2;  

Output:

Com1    5
Com2    3

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

1. SUM()  
2. or  
3. SUM( [ALL|DISTINCT] expression )  

Example: SUM()

1. SELECT SUM(COST)  
2. FROM PRODUCT_MAST;  

Output:

670

Example: SUM() with WHERE

1. SELECT SUM(COST)  
2. FROM PRODUCT_MAST  
3. WHERE QTY>3;  

Output:

320

Example: SUM() with GROUP BY

1. SELECT SUM(COST)  
2. FROM PRODUCT_MAST  
3. WHERE QTY>3  
4. GROUP BY COMPANY;  

Output:

Com1    150
Com2    170

Example: SUM() with HAVING

1. SELECT COMPANY, SUM(COST)  
2. FROM PRODUCT_MAST  
3. GROUP BY COMPANY  
4. HAVING SUM(COST)>=170;  

Output:

Com1    335
Com3    170

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

Syntax

1. AVG()  
2. or  
3. AVG( [ALL|DISTINCT] expression )  

Example:

1. SELECT AVG(COST)  
2. FROM PRODUCT_MAST;  

Output:

67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Syntax

1. MAX()  
2. or  
3. MAX( [ALL|DISTINCT] expression )  

Example:

1. SELECT MAX(RATE)  
2. FROM PRODUCT_MAST;  

30 

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Syntax

1. MIN()  
2. or  
3. MIN( [ALL|DISTINCT] expression )  

Example:

1. SELECT MIN(RATE)  
2. FROM PRODUCT_MAST;  

Output:

10