MYSQL
Aggregate functions/Group functions/ Multiple row functions
These functions work with data of multiple rows or groups of rows at a time and
return a single result for each group of rows.
There are five group functions in MySQl – SUM(), MAX(), MIN(), AVG() AND COUNT().
These group functions operate on the values of the column/expression passed as
an argument. For example
ARGUMENT passed to the function which adds the values
SUM(SAL) of the SAL column in the table.
1. MAX() function returns the MAXIMUM (largest) of the values in the specified
column/expression.
2. MIN() function returns the MINIMUM (smallest) of the values in the specified
column/expression.
3. AVG() function returns the AVERAGE of the values in the specified
column/expression.
4. SUM() function returns the SUM (total) of the values in the specified
column/expression.
5. COUNT() function returns the number of non-null values in the specified
column or expression. If the argument is an *, then COUNT(*) returns the total
number of rows satisfying the condition (if any) in the table irrespective of
whether the values are null or non-null.
Consider the EMPL table given below:
Page | 1
Examples
a. Find the total salary earned by all employees.
b. Display the lowest salary earned by the employees.
c. Display the total number of employees who are clerks.
NOTE: count(*) counts the total number of rows.
d. Display the total number of unique jobs in the EMPL table.
The DISTINCT keyword eliminates duplicate values in the job field of the table.
It considers only one occurrence of duplicate data.
e. Display the highest salary earned by Salesman in the EMPL table.
Page | 2
What is the output of the following?
a. Select MAX(sal + 100) from empl;
This example shows that the group functions can accept an expression as a
value. Here sal+100 is an expression.
b. Select COUNT(comm) from empl;
The COUNT() function will count the number of values in the COMM field
which are not null. Null values will be ignored. 0 (zero) is a numeric value so it
will be counted.
c. Select COUNT(*) from empl;
COUNT(*) will count the number of rows in the table.
d. Select AVG(comm) from empl;
The AVG() function will return the average of non-null values in the COMM
field of the table. The average will be calculated as
(1300+1500+2400+0+1000)/5;
There are 5 values in COMM field which are non-null.
Page | 3
Aggregate functions and NULL values:
The aggregate functions do not take NULL into consideration. NULL is simply ignored
by all the aggregate functions.
Q1. Consider the following queries:
Select COUNT(*) from EMP;
and
Select COUNT(Ename) from EMP;
The above queries give the result 10 and 8 respectively. What may be the
possible reason for this?
Ans. In the first query, COUNT(*) will return the total number of rows in the table.
Hence the result is 10.
In the second query, COUNT(Ename) will return the total number of non-NULL
values in the Ename column. There are 8 non-NULL values in the Ename
column, hence the result is 8. There are 2 NULL values in the Ename column
(as the total number of rows in the table is 10).
************
Page | 4
