SQL Aggregate Operators

In SQL, the aggregate operators perform a calculation on multiple rows or multiple values of a single column and give the output a single value.

Here, multiple rows of data are grouped and form a single data.

Types of Aggregate Operators

In SQL, there are five types of aggregate functions/operators to perform the operations on certain data.

1. sum()
2. avg()
3. max()
4. min()
5. count()

In SQL, The aggregate functions ignore the NULL values when it performs the calculations on multiple rows, except for the count() operator.

In this concept, we can also use the aggregate operators with the group by ang having classes to combine the data of the select statement.

Sum() Operators

The sum() function gives the total sum of the selected columns of a table

This operator mainly works on the numeric columns only.

Syntax:

sum()

Or

sum([all | distincts]expression)

Example: Let’s take an employee table and perform the sum() operation on it

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

Query: Select sum(salary) from employee;

Output:

SUM(SALARY)

260000

Query: Select sum(eid) from employee;

Output:

SUM(ID)

94

Query: Select sum(age) from employee;

Output:

SUM(AGE)

240

Using group by:

• Select sum(salary), age from employee group by age;

SUM(SALARY)	AGE
10000 20000 30000 40000 50000 110000	30 32 35 37 40 33

• Select sum(salary), id from employee group by id;

SUM(SALARY)	AGE
10000 70000 40000 20000 50000 70000	11 13 14 12 15 16

• Select sum(salary), name from employee group by name;

SUM(SALARY)	NAME
50000 20000 70000 40000 70000 10000	James John Jill Rose Alex Jack

Using having clause:

• Select sum(salary), idfrom employee1 group by id having sum(salary)>40000;

SUM(SALARY)	ID
70000 50000 80000	13 15 16

• Select sum(age) from employee group by id having sum(id)>12;

SUM(ID)	NAME
15 26 14 16	James jill rose Alex

Avg() Operators

The avg() function gives the total average of the selected columns of a table

This operator mainly works on the numeric columns, and it returns the average of all non-null values

Syntax:

avg()

Or

avg([all | distincts]expression)

Example: Let’s take an employee table and perform the avg() operation on it

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

Query: Select avg(salary) from employee;

Output:

AVG(SALARY)

37142.8571

Query: Select avg(id) from employee;

Output:

AVG(ID)

13.4285714

Query: Select avg(age) from employee;

Output:

AVG(AGE)

34.2857143

Using group by:

• Select avg(salary), age from employee group by age;

AVG(SALARY)	AGE
10000 20000 30000 40000 50000 550000	30 32 35 37 40 33

• Select avg(salary), id from employee group by id;

AVG(SALARY)	AGE
10000 35000 40000 20000 50000 70000	11 13 14 12 15 16

• Select avg(salary), name from employee group by name;

SUM(SALARY)	NAME
50000 20000 35000 40000 70000 10000	James John Jill Rose Alex Jack

Using having clause:

• Select avg(salary), id from employee1 group by id  having avg(salary)>40000;

AVG(SALARY)	ID
50000 70000	15 16

• Select avg(id),name from employee group by name having avg(id)>12;

AVG(ID)	NAME
15 13 14 16	James Jill Rose Alex

Max() Operators

The max() function gives the highest or maximum value of the selected columns of a table, and it is a set of all non-null values.

Syntax:

max()

Or

max([all | distincts]expression)

Example:

Let’s take an employee table and perform the max() operation on it

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

Query: Select max(salary) from employee;

Output:

MAX(SALARY)

70000

Query: Select  max(id) from employee;

Output:

MAX(ID)

16

Query: Select max(age) from employee;

Output:

MAX(AGE)

40

Using group by:

• Select max(salary), age from employee group by age;

MAX(SALARY)	AGE
10000 20000 30000 40000 50000 70000	30 32 35 37 40 33

• Select max(salary), id from employee group by id;

MAX(SALARY)	AGE
10000 40000 40000 20000 50000 70000	11 13 14 12 15 16

• Select max(salary), name from employee group by name;

MAX(SALARY)	NAME
50000 20000 40000 40000 70000 10000	James John Jill Rose Alex Jack

Using having clause:

• select max(salary), id from employee1 group by id having max(salary)>40000;

MAX(SALARY)	ID
50000 70000	15 16

• Select max(id), name from employee group by name having max(id)>12;

AVG(ID)	NAME
15 13 14 16	James Jill Rose Alex

Min() Operators

The min() function gives the lowest or minimum value of the selected columns of a table, and it is a set of all non-null values.

Syntax:

min()

Or

max([all | distincts]expression)

Example:

Let’s take an employee table and perform the min() operation on it

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

Query: Select max(salary) from employee;

Output:

MIN(SALARY)

10000

Query: Select max(id) from employee;

Output:

MIN(ID)

11

Query: Select max(age) from employee;

Output:

MIN(AGE)

30

Using group by:

• Select min(salary), age from employee group by age;

MAX(SALARY)	AGE
10000 20000 30000 40000 50000 70000	30 32 35 37 40 33

• Select min(salary), id from employee group by id;

MAX(SALARY)	AGE
10000 40000 40000 20000 50000 70000	11 13 14 12 15 16

• Select min(salary), name from employee group by name;

MIN(SALARY)	NAME
50000 20000 30000 40000 70000 10000	James John Jill Rose Alex Jack

Using having clause:

• select min(salary), id from employee1 group by id  having min(salary)>40000;

MIN(SALARY)	ID
50000 70000	15 16

• Select max(id), name from employee group by name having min(id)>12;

AVG(ID)	NAME
15 13 14 16	James Jill Rose Alex

Count() Operators

The count() function gives the number of rows in the table. It is a set  of all non-null values

Syntax:

count()

Or

count([all |distincts]expression)

Example:

Let’s take an employee table and perform the count() operation on it

ID	NAME	AGE	SALARY
11	Jack	30	10000
12	John	32	20000
13	Jill	35	30000
14	Rose	37	40000
15	James	40	50000
13	Jill	33	40000
16	Alex	33	70000

Query: Select count(salary) from employee;

Output:

COUNT(SALARY)

7

Query: Select count(id) from employee;

Output:

COUNT(ID)

7

Query: Select count(age) from employee;

Output:

COUNT(AGE)

7

Using group by:

• Select max(salary), age from employee group by age;

MAX(SALARY)	AGE
1 1 1 1 1 2	30 32 35 37 40 33

• Select max(salary), id from employee group by id;

MAX(SALARY)	AGE
1 1 1 1 1 1	11 13 14 12 15 16

• Select max(salary), name from employee group by name;

MAX(SALARY)	NAME
1 1 1 1 1 1	James John Jill Rose Alex Jack

Using having clause:

1. select count(salary), id from employee1 group by id having count(salary)>40000;
   no rows selected
2. Select max(id), name from employee group by name having max(id)>12;
   no rows selected