SELECT Statement

SELECT statement with GROUP BY clause

The GROUP BY clause is used to create one output row per each group and produces summary values for the selected columns, as shown below.

SELECT type
FROM Books
GROUP BY type

Here is an example using the above statement.

SELECT type AS 'Type', MIN(price) AS 'Minimum Price'
FROM Books
WHERE royalty > 10
GROUP BY type

If the SELECT statement includes a WHERE criterion where price is not null,

SELECT type, price
FROM Books
WHERE price is not null

then a statement with the GROUP BY clause would look like this:

SELECT type AS 'Type', MIN(price) AS 'Minimum Price'
FROM Books
WHERE price is not null
GROUP BY type


Using COUNT with GROUP BY

We can use COUNT to tally how many items are in a container. However, if we want to count different items into separate groups, such as marbles of varying colours, then we would use the COUNT function with the GROUP BY command.

The below SELECT statement illustrates how to count groups of data using the COUNT function with the GROUP BY clause.

SELECT COUNT(*)
FROM Books
GROUP BY type


Using AVG and SUM with GROUP BY

We can use the AVG function to give us the average of any group, and SUM to give the total.

Example #1 uses the AVG FUNCTION with the GROUP BY type.

SELECT AVG(qty)
FROM Books
GROUP BY type

Example #2 uses the SUM function with the GROUP BY type.

SELECT SUM(qty)
FROM Books
GROUP BY type

Example #3 uses both the AVG and SUM functions with the GROUP BY type in the SELECT statement.

SELECT 'Total Sales' = SUM(qty), 'Average Sales' = AVG(qty), stor_id
FROM Sales
GROUP BY StorID ORDER BY  'Total Sales'