Using Aggregate Functions and Group By clause in MySQL

Using Aggregate Functions and Group By Clause in MySQL

Aggregate Functions are used to calculate summarized data such as an average or a sum. MySQL provides the common aggregate functions plus some MySQL specific ones. In this tutorial, we'll only cover the most often used aggregate functions.

Note that all aggregate functions, with the exception of COUNT(*) function, ignore NULL values in the columns.

When summarize values on a column, aggregate Functions can be used for the entire table or for groups of rows in the table. When applied to groups of rows, use GROUP BY clause with the SELECT statement.

No.	Name	Description
1	AVG	Return the average value of the argument which should be numeric type of data. Practice #1-1 Practice #1-2 Practice #1-3
2	COUNT(column_name)	Return a count of the number of rows in a column. NULL values in the column are ingored. Practice #2-1 Practice #2-2
3	COUNT(DISTINCT column_name)	Return the count of how many distinct values in a column. Practice #3-1 Practice #3-2
4	COUNT(*)	Return a count for the number of rows in the entire table or groups in the table. NULL values are counted. The * character means ALL. Practice #4-1 Practice #4-2
5	MAX()	Return the maximum value within a group of values. See Practice #5.
6	MIN()	Return the minimum value within a group of values. See Practice #6.
7	SUM()	Return the sum for each group of values See Practice #7.
8	GROUP_CONCAT(expr)	GROUP_CONCAT returns a concatenated string of non-NULL values for each group of rows in a table. See Practice #8-1, Practice #8-2

Practice #1-1: Using AVG function in orders table for the entire table.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query calculates the average 
freight for all orders.
*/
select avg(Freight) as average_freight
from orders;

Query result set - 1 row returned:
Using AVG function for an entire table.

Practice #1-2: Using AVG function in orders table for one customer.

/*
This query calculates the average freight 
in orders table for one customer only .
*/
select avg(Freight) as average_freight
from orders
where CustomerID = 'ALFAA';

Query result set - 1 row returned:
Using AVG function in orders table for one customer.

Practice #1-3: Using AVG function in orders table for each customer.

/*
This query calculates the average freight in orders 
table for each customer. GROUP BY clause is used 
to group all orders for each individual customer.
*/
select CustomerID, avg(Freight) as average_freight
from orders
group by CustomerID;

Query result set - 89 rows returned:
Using AVG function in orders table for each customer.

Practice #2-1: Using COUNT function.

/*
This query counts how many employees 
Northwind Trader has.
*/
select count(EmployeeID) as num_of_employees
from employees;

Query result set - 9 rows returned:
Using COUNT function.

Practice #2-2: Using COUNT function on a column where NULL value exists.

/*
This query is similar to the one in Practice #2-1 
above except that it counts ReportsTo column. 
 
Because the ReportsTo value for employee Fuller Andrew
is NULL so the result returned 8 rather than 9.
 
This shows that COUNT(column_name) function does not
count NULL values if they exist in the column.
*/
select count(ReportsTo)
from employees;

Query result set - 8 rows returned:
Using COUNT function on a column where NULL value exists.

Practice #3-1: Using COUNT(DISTINCT) function.

/*
This query returns how many distinct countries
the suppliers are from.
*/
select count(distinct Country) as num_of_countries
from suppliers;

Query result set - 1 row returned:
Using COUNT(DISTINCT) function.

Practice #3-2: Using COUNT(DISTINCT) function with NULL values.

/*
Query 1:
 
This query returns 2 and shows you that 
NULL values are not counted when using DISTINCT
keyword in COUNT function.
*/
select count(distinct ReportsTo) as num_of_managers
from employees;
 
/*
Query 2:
 
This query shows how many distinct values are
in ReportsTo column. Note that NULL is in the 
result but not counted as a distinct value in
Query 1 above.
*/
select distinct ReportsTo
from employees;

Query 1 result set - 1 row returned:
Using COUNT(DISTINCT) function with NULL values.

Query 2 result set - 3 rows returned:
Using COUNT(DISTINCT) function with NULL values.

Practice #4-1: Using COUNT(*) function.

/*
This query counts the number of rows in employees 
table. All rows are counted no matter whether 
NULL values exist in some of its columns.
*/
select count(*)
from employees;

Query result set - 9 rows returned:
Using COUNT(*) function.

Practice #4-2: Using COUNT(*) function to count NULL values.

/*
This query only counts where ReportsTo column is null.
It shows that count(*) also returns the row that 
contains NULL value.
*/
select count(*)
from employees
where ReportsTo is null;

Query result set - 1 row returned:
Using COUNT(*) function to count NULL values.

Practice #5: Using MAX() function.

/*
This query returns the maximum unit price 
in products table.
*/
select max(UnitPrice) as max_unit_price
from products;

Query result set - 1 row returned:
Using MAX() function

Practice #6: Using MIN() function.

/*
This query returns the minimum unit price 
in products table.
*/
select min(UnitPrice) as min_unit_price
from products;

Query result set - 1 row returned:
Using MIN() function

Practice #7: Using SUM() function.

/*
This query returns the total number of units
in stock if the product has been discontinued.
*/
select sum(UnitsInStock) total_discontinued_unit_in_stock
from products
where Discontinued = 'y';

Query result set - 1 row returned:
Using SUM() function

Practice #8-1: Using GROUP_CONCAT() function.

GROUP_CONCAT returns a concatenated string of non-NULL values for each group of rows in a table. Although GROUP_CONCAT is not an ANSI and an ISO standard function (which means it's an MySQL extension), it's very useful in the way that it saves you time to use another language such as PHP to loop and build a concatenated string by yourself.

The full syntax of GROUP_CONCAT function is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

/*
This query uses group_concat function to retrieve all 
SupplierID values for each group of product categories
and then return the SupplierID values in a CSV 
(Comma Separated Values) string. 
 
The default string separator is a comma (","). You can 
eliminate the separator altogether by specifying a
SEPARATOR character(s).
 
Note that the concatenated SupplierIDs contains duplicated
values which is removed in Practice #8-2 by using distinct
keyword.
*/
select CategoryID,
       group_concat(SupplierID) as concat_supplier_id
from products
group by CategoryID;

Query result set - 8 rows returned:
Using GROUP_CONCAT() function

Practice #8-2: Using GROUP_CONCAT() function for distinct values.

/*
This query is similar to Practice #8-1 except that it has 
removed duplicates of SupplierID values in the CSV. Also it
orders the SupplierID values in ascending order.
*/
select c.CategoryID,
       c.CategoryName,
       group_concat(distinct p.SupplierID order by p.SupplierID)
       as concat_supplier_id
from products as p
inner join categories as c on c.CategoryID=p.CategoryID
group by p.CategoryID;

Query result set - 8 row returned:
Using GROUP_CONCAT() function for distinct values

Next tutorial: More About Using Group By Clause in MySQL
Back to Section Index: Summarize Data
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search