More About Using Group By clause in MySQL

More About Using Group By Clause in MySQL

In last tutorial, we have had a glimpse about how to use Group By clause. In this tutorial, we are going to look at more closely to the requirements of using Group By clause.

Rows in a table can be grouped by using one or more columns. For example, rows in products table can be grouped by CategoryID values so that we can see summarized information for each group of the product categories (e.g. what is the total number of units in stock for each product category). When using Group By clause, consider the following facts and guidelines:

For each group of rows, a single row of values is produced. See Practice #1.
If WHERE clause is used, only the rows that satisfy the WHERE clause conditions are grouped. Also, the WHERE clause must be listed before the GROUP BY clause. See Practice #2.
If the column used in GROUP BY clause contains null values, the null values are treated as a group. To exclude this group, use IS NOT NULL in the WHERE clause. See Practice #3-1, #3-2.
Function(s) other than aggregation functions can be used in GROUP BY clause. See Practice #4
By using GROUP BY clause, rows can be converted to columns which mimics pivot table effect in MS Excel. See Practice #5-1, #5-2
Column alias can be used in GROUP BY clause. This is different to most other database management systems such as Oracle and SQL Server. See Practice #6-1, #6-2, #6-3

Practice #1: A single row of values is produced for each group.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query shows that, for each group of product 
categories, only one row of values is produced.
*/
select c.CategoryID,
       c.CategoryName,
       sum(UnitsInStock) as "Total units in stock"       
from products as p
inner join categories as c on c.CategoryID=p.CategoryID
group by p.CategoryID;

Query result set - 8 rows returned:
A single row of values is produced for each group.

Practice #2: Using WHERE clause with GROUP BY.

/*
This query is similar to Practice #1 but used a WHERE
clause to only select the first 5 product categories to
group. Note that WHERE clause is listed before GROUP BY
clause.
*/
select c.CategoryID,
       c.CategoryName,
       sum(UnitsInStock) as "Total units in stock"       
from products as p
inner join categories as c on c.CategoryID=p.CategoryID
where c.CategoryID in (1, 2, 3, 4, 5)
group by p.CategoryID;

Query result set - 5 rows returned:
Using WHERE clause with GROUP BY.

Practice #3-1: NULL values are treated as a group.

/*
This query shows that, if the column used in GROUP BY 
clause contains null values, the null values are 
treated as a group.
 
There is one employee who does not need to report to anyone.
*/
select ReportsTo, count(*) as Num_of_employees
from employees
group by ReportsTo;

Query result set - 3 rows returned:
NULL values are treated as a group.

Practice #3-2: NULL values are removed.

/*
This query shows that null values are removed by 
using is not null operator.
*/
select ReportsTo, count(*) as Num_of_employees
from employees
where ReportsTo is not null
group by ReportsTo;

Query result set - 3 rows returned:
NULL values are removed.

Practice #4: Using case statement in GROUP BY clause.

/*
This query shows that case statement is used in 
GROUP BY clause to list the number of units in stock
for each product category and supplier's continent.
 
Note that, if only s.Country (not the case statement) 
is used in the GROUP BY, duplicated rows will exist 
for each product category and supplier continent.
*/
select c.CategoryName as "Product Category", 
       case when s.Country in 
                 ('UK','Spain','Sweden','Germany','Norway',
                  'Denmark','Netherlands','Finland','Italy','France')
                 then 'Europe'
            when s.Country in ('USA','Canada','Brazil') 
                 then 'America'
            else 'Asia-Pacific'
        end as "Supplier Continent", 
        sum(p.UnitsInStock) as UnitsInStock
from Suppliers as s 
inner join Products as p on p.SupplierID=s.SupplierID
inner join Categories as c on c.CategoryID=p.CategoryID 
group by c.CategoryName, 
         case when s.Country in 
                 ('UK','Spain','Sweden','Germany','Norway',
                  'Denmark','Netherlands','Finland','Italy','France')
                 then 'Europe'
              when s.Country in ('USA','Canada','Brazil') 
                 then 'America'
              else 'Asia-Pacific'
         end;

Query result set - 21 rows returned:
Using case statement in GROUP BY clause.

Practice #5-1: Converting rows to columns by using GROUP BY clause.

/*
This query shows how to use case statement inside 
sum function to convert rows to columns.
 
Units in stock for each continent is retrieved and
each continent now has a column heading associated
with it.
 
If you compare with the result from Practice #4-2
above, you can see that rows in Supplier Continent
column have been converted to columns in this query's
result.
*/
select c.CategoryName as "Product Category", 
       sum(case when s.Country in 
                 ('UK','Spain','Sweden','Germany','Norway',
                  'Denmark','Netherlands','Finland','Italy','France')
                 then p.UnitsInStock end) as Europe,
       sum(case when s.Country in ('USA','Canada','Brazil') 
                 then p.UnitsInStock end) as America,
       sum(case when s.Country in ('Japan','Australia','Singapore')
                 then p.UnitsInStock end) as "Asia-Pacific"
from Suppliers as s 
inner join Products as p on p.SupplierID=s.SupplierID
inner join Categories as c on c.CategoryID=p.CategoryID
group by c.CategoryName;

Query result set - 8 rows returned:
Converting rows to columns by using GROUP BY clause.

Practice #5-2: Converting rows to columns and nulls to zero.

/*
This query is the same as Practice #5-1 except that it used
IFNULL function to convert null values that are shown in 
Practice #5-1 to zero.
*/
select c.CategoryName as "Product Category", 
       ifnull(sum(case when s.Country in 
                 ('UK','Spain','Sweden','Germany','Norway',
                  'Denmark','Netherlands','Finland','Italy','France')
                 then p.UnitsInStock end), 0) as Europe,
       ifnull(sum(case when s.Country in ('USA','Canada','Brazil') 
                 then p.UnitsInStock end), 0) as America,
       ifnull(sum(case when s.Country in ('Japan','Australia','Singapore')
                 then p.UnitsInStock end), 0) as "Asia-Pacific"
from Suppliers as s 
inner join Products as p on p.SupplierID=s.SupplierID
inner join Categories as c on c.CategoryID=p.CategoryID
group by c.CategoryName;

Query result set - 8 rows returned:
Converting rows to columns and nulls to zero by using GROUP BY clause.

Practice #6-1: Using column alias in GROUP BY clause.

/*
This query calculates the dollar amount sold for each product.
 
Column alias ShippedQuarter is used in GROUP BY clause. Most 
other database management systems (eg. Oracle, SQL Server) will
raise an error if you use column alias.
*/
select c.CategoryName, 
       p.ProductName, 
       concat('Qtr ', quarter(o.ShippedDate)) as ShippedQuarter,
       concat('$', round(sum(od.UnitPrice*od.Quantity*(1-od.Discount)), 2)) 
       as ProductSales
from orders as o 
inner join order_details as od on o.OrderID = od.OrderID
inner join products as p on od.ProductID = p.ProductID
inner join categories as c on c.CategoryID = p.CategoryID
where o.ShippedDate between '1996-5-1' and '1996-10-31'
group by c.CategoryName, p.ProductName, ShippedQuarter
order by ShippedQuarter, c.CategoryName, p.ProductName;

Query result set - 111 rows returned:
Using column alias in GROUP BY clause.

Practice #6-2: Column in select list but not in GROUP BY clause.

/*
This query is similar to the one above except that 
ShippedQuarter is not in GROUP BY clause. 
 
It's a surprise that MySQL server didn't raise an 
error but silently grouped the same products together
and used the first available quarter for the row. This
is a rather bad database behavior. For other DBMS 
systems (eg. Oracle, SQL Server), the ship quarter 
column must be used in GROUP BY clause or error will 
occur.
*/
select c.CategoryName, 
       p.ProductName, 
       concat('Qtr ', quarter(o.ShippedDate)) as ShippedQuarter,
       concat('$', round(sum(od.UnitPrice*od.Quantity*(1-od.Discount)), 2)) 
       as ProductSales
from orders as o 
inner join order_details as od on o.OrderID = od.OrderID
inner join products as p on od.ProductID = p.ProductID
inner join categories as c on c.CategoryID = p.CategoryID
where o.ShippedDate between '1996-5-1' and '1996-10-31'
group by c.CategoryName, p.ProductName
order by ShippedQuarter, c.CategoryName, p.ProductName;

Query result set - 71 rows returned:
Column in select list but not in GROUP BY clause.

Practice #6-3: Another pivot table query result.

/*
This query is modified from the query named as 
"Quarterly Orders by Product" in MS Access 
version of the Northwind database. It returns
the exact same result as it in Access.
*/
select p.ProductName, 
       c.CompanyName,
       year(o.OrderDate) as OrderYear,
       ifnull(round(sum(case when quarter(o.OrderDate)=1 
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr1,
       ifnull(round(sum(case when quarter(o.OrderDate)=2 
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr2,
       ifnull(round(sum(case when quarter(o.OrderDate)=3 
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr3,
       ifnull(round(sum(case when quarter(o.OrderDate)=4
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr4
from orders as o
inner join customers as c on c.CustomerID=o.CustomerID
inner join order_details as od on od.OrderID=o.OrderID
inner join Products as p on p.ProductID=od.ProductID
where o.OrderDate between '1997-1-1' and '1997-12-31'
group by p.ProductName, c.CustomerID, OrderYear;

Query result set - 947 rows returned:
Pivot table query result.

Next tutorial: Using GROUP BY with HAVING Clause
Back to Section Index: Summarize Data
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search