Module 2: Aggregating & Grouping Data•40 min

GROUP BY Fundamentals

Progress Tracking

One Number Per Group

In the last lesson, you got a single total for the whole table. Useful, but limited. Real business questions usually need breakdowns: revenue by month, customers by region, headcount by department.

GROUP BY is how you get there. It splits your data into groups, then applies your aggregate function to each group separately.

Your First GROUP BY

SQL

SELECT
  cust_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY cust_id;

Table: techcorp_workforce

id	first_name	last_name	department	salary	phone_number	joining_date
1	Sarah	Mitchell	HR	95000	555-0101	2021-03-15
2	Michael	Chen	HR	88000	555-0102	2022-06-01
3	Emily	Rodriguez	HR	82500		2021-09-20
4	David	Park	HR	80000	555-0104	2023-01-10
5	Lisa	Thompson	HR	65000		2021-04-05

Tables: techcorp_workforce

Instead of one count for everyone, you get one count per department. The database groups all HR employees, counts them, and then does the same for Admin, Account, and so on.

The Golden Rule

Here’s something many beginners find confusing: when you use GROUP BY, every column in your SELECT must either be in your GROUP BY clause or inside an aggregate function. No exceptions.

SQL

-- This works: department is in GROUP BY
SELECT
  department,
  AVG(salary)
FROM techcorp_workforce
GROUP BY department;

SQL

-- This fails: first_name isn’t grouped or aggregated
SELECT
  department,
  first_name,
  AVG(salary)
FROM techcorp_workforce
GROUP BY department;

Why? Think about it: if you’re grouping by department, and HR has four employees, which first name should SQL show? It can’t pick one, so it refuses to run.

Most common GROUP BY error

If your query fails, check that every non-aggregated column is in your GROUP BY.

Sorting Your Groups

GROUP BY results come back in no particular order. Usually, you want them sorted — either alphabetically or by the aggregate value:

SQL

-- Sort by department name
SELECT
  department,
  SUM(salary) AS dept_payroll
FROM techcorp_workforce
GROUP BY department
ORDER BY department;

MacBookPro User Event Count

Group by event name and use COUNT.

Table: playbook_events

user_id	occurred_at	event_type	event_name	location	device
6991	2014-06-09 18:26:54	engagement	home_page	United States	iphone 5
18851	2014-08-29 13:18:38	signup_flow	enter_info	Russia	asus chromebook
14998	2014-07-01 12:47:56	engagement	login	France	hp pavilion desktop
8186	2014-05-23 10:44:16	engagement	home_page	Italy	macbook pro
9626	2014-07-31 17:15:14	engagement	login	Russia	nexus 7

Tables: playbook_events

Artist Appearance Count

Group by artist and count how many times they appear.

Table: spotify_worldwide_daily_song_ranking

id	position	trackname	artist	streams	url	stream_date	region
303651	52	Heart Won't Forget	Matoma	28047	https://open.spotify.com/track/2of2DM5LqTh7ohmmVXUKsH	2017-02-04	no
85559	160	Someone In The Crowd - From "La La Land" Soundtrack	Emma Stone	17134	https://open.spotify.com/track/7xE4vKvjqUTtHyJ9zi0k1q	2017-02-26	fr
1046089	175	The Greatest	Sia	10060	https://open.spotify.com/track/7xHWNBFm6ObGEQPaUxHuKO	2017-03-06	cl
350824	25	Unforgettable	French Montana	46603	https://open.spotify.com/track/3B54sVLJ402zGa6Xm4YGNe	2017-10-01	no
776822	1	Bad and Boujee (feat. Lil Uzi Vert)	Migos	1823391	https://open.spotify.com/track/4Km5HrUvYTaSUfiSGPJeQR	2017-01-27	us

Tables: spotify_worldwide_daily_song_ranking

Top Ranked Songs

You’ll need GROUP BY with a WHERE filter here.

Table: spotify_worldwide_daily_song_ranking

id	position	trackname	artist	streams	url	stream_date	region
303651	52	Heart Won't Forget	Matoma	28047	https://open.spotify.com/track/2of2DM5LqTh7ohmmVXUKsH	2017-02-04	no
85559	160	Someone In The Crowd - From "La La Land" Soundtrack	Emma Stone	17134	https://open.spotify.com/track/7xE4vKvjqUTtHyJ9zi0k1q	2017-02-26	fr
1046089	175	The Greatest	Sia	10060	https://open.spotify.com/track/7xHWNBFm6ObGEQPaUxHuKO	2017-03-06	cl
350824	25	Unforgettable	French Montana	46603	https://open.spotify.com/track/3B54sVLJ402zGa6Xm4YGNe	2017-10-01	no
776822	1	Bad and Boujee (feat. Lil Uzi Vert)	Migos	1823391	https://open.spotify.com/track/4Km5HrUvYTaSUfiSGPJeQR	2017-01-27	us

Tables: spotify_worldwide_daily_song_ranking

Key Takeaways

GROUP BY splits your data into groups before aggregating
Every SELECT column must be in GROUP BY or an aggregate function
You can group by multiple columns for finer breakdowns
Use ORDER BY to sort your results (it comes after GROUP BY)

What’s Next

GROUP BY creates your groups, but what if you only want some of them? Say, only departments with more than five employees, or only months with revenue over $100K. You can’t use WHERE for this — WHERE filters rows before grouping happens. You need something that filters after. That’s HAVING, and it’s next.

Next upContinue →

Filtering Groups with HAVING

25 min