Learning Goals
By the end of this section you will:
know how use the GROUP BY clause to perform aggregation calculations on subgroup within the results.
know how to filter the grouped results using the HAVING clause.
GROUP BY Clause 📝¶
The SQL GROUP BY statement is used to group rows that have the same values in specified columns into summary rows, often in conjunction with aggregate functions to perform calculations on each group. While the SQL HAVING statement is used to filter groups of rows created by the GROUP BY clause based on specified conditions, typically involving aggregate functions.
Go to the W3schools Tutorial on the GROUP BY Statement and complete thier exercises.
HAVING Clause¶
The HAVING clause is used to filter groups of records created by the GROUP BY clause based on a specified condition.
Go to the W3schools Tutorial on the HAVING Clause and complete their exercises.
GROUP BY and HAVING Activities
Using the Movies database
How many directors are there from each country?
How many movies does each member number have on hire?
How many movies are stocked from each year?
Using the School database
What is the average result for each subject(code)?
How many boys and girls in each grade?
Using the Shares database
How many companies are there in each risk category?
What is the cheapest, medium risk and cheapest low risk share on the New York Stock Exchange?
Using the Chinook database
How many customers are there from each country with more than one customer?
What are the AlbumIds of all ablums that run for more than an hour (3600 secs)?
Provide an alphabetical list of countries and total sales if their sales where over $100