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.
person | ||||
id | firstname | lastname | title | dob |
1 | Fred | Bloggs | Mr | 1980-05-05 |
2 | Simon | Smith | Mr | 1960-04-01 |
3 | Freida | Bloggs | Mrs | 1970-10-12 |
4 | Fiona | Jones | Miss | 1985-05-19 |
person_order | |||
id | order_date | order_total | person_id |
1 | 2016-01-19 | 101.60 | 1 |
2 | 2016-02-02 | 256.54 | 2 |
3 | 2016-03-05 | 222.10 | 4 |
4 | 2016-05-23 | 12.45 | 1 |
5 | 2016-07-29 | 325.65 | 2 |
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.
person_id | count |
1 | 2 |
2 | 2 |
4 | 1 |
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.
id | firstname | lastname | count |
1 | Fred | Bloggs | 2 |
2 | Simon | Smith | 2 |
4 | Fiona | Jones | 1 |
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.
id | firstname | lastname | count | max | sum |
1 | Fred | Bloggs | 2 | 101.60 | 114.05 |
2 | Simon | Smith | 2 | 325.65 | 582.19 |
4 | Fiona | Jones | 1 | 222.10 | 222.10 |