Grouping Part 2

Part of the Fundamentals section of Coddy's SQL journey — lesson 37 of 72.

The WHERE keyword runs a condition on each record separately. For example:

SELECT * FROM table1
WHERE col1 > col2

The col1 > col2 will run on every record and check if it is met.

But what if we want to filter aggregate results? For example:

SELECT category, AVG(price) FROM table1
WHERE AVG(price) > 40
GROUP BY category

This will not work because WHERE cannot check any aggregations. For that, we have the HAVING keyword. It filters data by the aggregate condition.

SELECT category, AVG(price) FROM table1
GROUP BY category
HAVING AVG(price) > 40

This will filter all the categories for which the average price is greater than 40.

If we want to combine HAVING and WHERE clause we will write:

SELECT category, AVG(price) FROM table1
WHERE price > 25
GROUP BY category
HAVING AVG(price) > 40

This will first go record by record and filter all the records for which the price is greater than 25, and only after that will it run the GROUP BY clause and filter every category for which the average price is greater than 40.

Challenge

Medium

Available tables and columns:
earthquakes: location, amplitude, period

The Richter scale is a logarithmic scale used to measure the magnitude of earthquakes.

We need to return the average magnitude for each location of all of the major earthquakes

A major earthquake is defined as:

The amplitude is greater or equal to 1
The period of the waves is greater or equal to 1 minute

For this challenge, we will use a different formula: M = (A/T)2 / T, which is equivalent to M = ((A/T)*(A/T)) / T.

Return the location and the average magnitude of each location while including only the major earthquakes (name the column avg_magnitude). Include in your result only the locations where the avg_magnitude is greater than 1

Round the results to 2 decimal places

Cheat sheet

The WHERE keyword filters individual records, while HAVING filters aggregate results after grouping:

SELECT category, AVG(price) FROM table1
GROUP BY category
HAVING AVG(price) > 40

To combine both clauses:

SELECT category, AVG(price) FROM table1
WHERE price > 25
GROUP BY category
HAVING AVG(price) > 40

WHERE filters records first, then GROUP BY groups the remaining data, and finally HAVING filters the grouped results.

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

Grouping Part 2

Cheat sheet

All lessons in Fundamentals