Grouping Part 1

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

We have calculated data for the entire field thus far, and now we will introduce the ability to calculate data for specific groups.

workers

If we write

SELECT AVG(age) as avg_age FROM workers

We will receive only the average age of all the workers together.

What if we want to calculate the average age for each area?

For that, we can use the GROUP BY keywords

SELECT area, AVG(age) as avg_age FROM workers
GROUP BY area

The result:

area	avg_age
A	36
B	34

Now we know that the average age in Area A is 36 and the average age in Area B is 34.

Challenge

Easy

Available tables and columns:
foods: name, type, pH

Calculate, for each food type, the average pH value.

Name the columns: type, ph_average.

Use ROUND(value, 2) to round the average to two decimal places.

Cheat sheet

Use GROUP BY to calculate aggregate functions for specific groups:

SELECT area, AVG(age) as avg_age FROM workers
GROUP BY area

This groups rows by the area column and calculates the average age for each group separately.

Use ROUND(value, decimal) to round numerical results to a specified number of decimal places:

SELECT ROUND(AVG(age), 2) FROM workers

Try it yourself

Test yourself

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