Stuart Banner - M358 - The GROUP BY clause

The GROUP BY clause

Allows the query to return values from a query to a specific column in a table:

SELECT region, COUNT(*)
FROM student
GROUP BY region;

The SELECT clause in a GROUP BY query can only contain value expressions consisting of the GROUP BY columns or aggregate functions.

If the GROUP BY facility was not there, each individual region would have to be counted, one by one:

SELECT COUNT(*)
FROM student
WHERE region = '1';   //repeated for each region resulting in numerous queries

Logical processing

Intermediate table (Cartesian product) formed by FROM giving all details from the student table
Rows are grouped by the GROUP BY clause as per the grouping condition
SELECT works differently with GROUP BY. Each set/group of rows is replaced by one row. For each set of rows in the group all columns except those specified are dispensed with. The aggregate function COUNT(*) counts the number of rows in the group to return the final table.
DISTINCT is redundant in a GROUP BY query

Group search conditions

The HAVING clause

Groups from a GROUP BY clause have to satisfy the HAVING clause (where specified) to be processed further. Similar to a WHERE clause but applied to a group of rows instead of individual rows.

SELECT country, COUNT(product), SUM(quantity)
FROM production
GROUP BY country
HAVING SUM(quantity) > 15000;

Logical processing

Intermediate table (Cartesian product) formed by FROM giving all details from the production table
As there is no WHERE clause restricting table rows the GROUP BY clause assembles the table into groups of rows linked by country
The HAVING clause is processed - each group of rows is tested for the search condition. Those groups that satisfy the condition are formed into the next intermediate table
The final table is produced by the SELECT clause

Any column referenced in a predicate in the search condition of a HAVING clause must be a grouping column or have an aggregate function applied.

The ORDER BY clause

Final tables are ordered as per the implementation related to how data is stored. Control over ordering can be specified by an ORDER BY clause in a query statement. There is no influence on table content, only presentation.

SELECT *
FROM staff;                      //No apparent ordering

SELECT *
FROM staff
ORDER BY name;                   //Ordering is ascending by default

SELECT *
FROM staff
ORDER BY name ASC;               //Including ASC ketword has no effect
                                   Compare the images above and below

SELECT *
FROM staff
ORDER BY name DESC;              //Keyword DESC reverses default ordering

Assigning names to expression results is the only way in which ordering can be achieved where the results of value expressions need ordering.

SELECT name, population/area AS pop_density
FROM country
ORDER BY pop_density;

Ordering can be specified using more than one column.

SELECT *
FROM enrolment
WHERE student_id NOT IN ('s01', 's04')
ORDER BY student_id, course_code DESC;

Move on to updating.

Comments, suggestions, ideas to
Stuart Banner