SQL ‘Group By’ Statement

An SQL ‘group by’ statement is used in conjunction with functions such as MIN, MAX, COUNT, SUM and AVG, discussed previously, to group results by one or more columns.

SELECT column_name1, column_name2, FUNCTION(column_name3)
FROM table_name
WHERE condition
GROUP BY column_name1, column_name2;

The ‘person‘ and ‘person_order‘ tables, from previous examples, can be used to demonstrate the use of the ‘group_by’ statement.

This first example uses just the ‘person_order’ table to demonstrate the ‘group by’ statement in its simplest form. It displays the ‘person_id’ and a count of how many orders there have been for that person. Note that the count column has been given a heading of ‘count’ and the results are ordered by ‘person_id’.

SELECT person_id, COUNT(*) AS 'count'
FROM person_order
GROUP BY person_id
ORDER BY person_id;

The results returned are as follows.

To give these results more meaning, the ‘person’ table could also be incorporated so that the ‘firstname’ and ‘lastname’ can be included.

SELECT person.id, person.firstname, person.lastname, 
       COUNT(*) AS 'count'
FROM person
INNER JOIN person_order
ON person.id=person_order.person_id
GROUP BY person.id, person.firstname, person.lastname
ORDER BY person.id;

The results are now more meaningful.

It is also possible to incorporate more than one function, such as a count of orders, the maximum order total and a sum of all order totals.

SELECT person.id, person.firstname, person.lastname, 
       COUNT(*) AS 'count', 
       MAX(person_order.order_total) AS 'max',
       SUM(person_order.order_total) AS 'sum'
FROM person
INNER JOIN person_order
ON person.id=person_order.person_id
GROUP BY person.id, person.firstname, person.lastname
ORDER BY person.id;

This now includes two extra columns, ‘max’ and ‘sum’, which show the maximum spent on an order and the total of all orders for a person.