GROUP BY: Grouping and Aggregating Data

Simple GROUP BY

GROUP BY with Multiple Aggregates

-- Get listening stats per user: count, average, max, min ratings
SELECT 
    user_id,
    COUNT(*) AS listen_count,      -- Counts ALL rows in group (including NULLs)
    AVG(rating) AS avg_rating,      -- AVG ignores NULL values
    MAX(rating) AS max_rating,      -- Finds highest non-NULL rating
    MIN(rating) AS min_rating       -- Finds lowest non-NULL rating
FROM Listens
GROUP BY user_id                    -- Each user gets their own stats

GROUP BY Multiple Columns

-- Count how many times each user played each song
SELECT 
    user_id,
    song_id,
    COUNT(*) AS play_count,
    AVG(rating) AS avg_rating
FROM Listens
GROUP BY user_id, song_id    -- multi-column GROUP BY: unique (user, song) pairs
ORDER BY user_id, song_id    -- Sort output: first by user, then by song

GROUP BY with WHERE

-- Count high-rated songs (>4.0) per user
SELECT 
    user_id,
    COUNT(*) AS high_rating_count,
    AVG(rating) AS avg_high_rating
FROM Listens
WHERE rating > 4.0            -- WHERE filters BEFORE grouping (NULL > 4.0 = false)
GROUP BY user_id              -- Groups only the filtered rows
ORDER BY user_id              -- Sort by user_id ascending (default)

In general, WHERE filters rows first, GROUP BY groups only the rows that survive, and the aggregates see only those rows.

GROUP BY with NULL Values

-- Count how many listens for each rating value (including NULL)
SELECT 
    rating,
    COUNT(*) AS COUNT
FROM Listens
GROUP BY rating               -- NULL values form their own group
ORDER BY rating               -- NULLs appear first or last (DB-dependent)

In general, all NULLs fall into one group, even though NULL = NULL is UNKNOWN in a WHERE test. ORDER BY puts that group first or last, depending on the database.

COUNT(*) vs COUNT(column)

-- Compare total listens vs rated vs timed listens per user
SELECT 
    user_id,
    COUNT(*) AS total_listens,           -- COUNT(*) includes NULL rows
    COUNT(rating) AS rated_listens,      -- COUNT(column) skips NULL values
    COUNT(listen_time) AS timed_listens  -- Different NULLs = different counts
FROM Listens
GROUP BY user_id
ORDER BY user_id

COUNT Variants:

• COUNT(*) = all rows (including NULLs)

• COUNT(column) = non-NULL values only

• COUNT(DISTINCT column) = unique non-NULL values

Key Rules

1. Simple Grouping: GROUP BY user_id creates one group per user

2. Multi-Column Grouping: GROUP BY user_id, song_id groups by combinations

3. Aggregate Scope: Functions like COUNT() operate within each group. SELECT user_id, COUNT(*), AVG(rating)

4. Execution Order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. SELECT runs fifth, after the groups exist.

5. NULL Grouping: NULL values form their own group

6. SELECT Restriction: Only GROUP BY columns and aggregates allowed in SELECT (else invalid: a non-aggregated column that is not in GROUP BY)

Common Mistakes

The Non-Grouped Column Error; UNSAFE queries

We grouped by user_id, so each user collapses to one row. But we also asked for song_id, and Mickey's one group holds three different songs. Which one goes in his single row?

Fix: add song_id to GROUP BY. Each (user, song) pair becomes its own group, so every song_id resolves to exactly one value.

-- Safe: every non-aggregate column is in GROUP BY
SELECT user_id, song_id, COUNT(*)
FROM Listens
GROUP BY user_id, song_id;

WHERE Can't See Aggregates

-- WRONG: WHERE can't filter on aggregates (they don't exist yet)
SELECT user_id, AVG(rating)
FROM Listens
WHERE AVG(rating) > 4.0
GROUP BY user_id

-- CORRECT: Use HAVING to filter groups after aggregation
SELECT user_id, AVG(rating)
FROM Listens
GROUP BY user_id
HAVING AVG(rating) > 4.0

Problem: WHERE executes before grouping; aggregates don't exist yet

The COUNT NULL Trap

-- Misleading: COUNT(rating) skips NULL ratings
SELECT user_id, 
       COUNT(rating) AS total_ratings  -- Skips NULLs!
FROM Listens
GROUP BY user_id

-- Better: Show both total rows and non-NULL ratings
SELECT user_id, 
       COUNT(*) AS total_listens,
       COUNT(rating) AS rated_listens
FROM Listens
GROUP BY user_id

Problem: COUNT(column) behavior with NULL often unexpected

Redundant DISTINCT

-- REDUNDANT: GROUP BY already ensures unique user_id values
SELECT DISTINCT user_id, COUNT(*)
FROM Listens
GROUP BY user_id

-- SIMPLER: GROUP BY alone creates unique groups
SELECT user_id, COUNT(*)
FROM Listens
GROUP BY user_id

Problem: GROUP BY already ensures unique group values