GROUP BY: Grouping and Aggregating Data

Simple GROUP BY

-- Count how many songs each user has listened to
SELECT user_id, COUNT(*) AS listen_count
FROM Listens
GROUP BY user_id  -- one group per unique user_id value

• One group per unique value

• Aggregates work per group

• Groups are isolated from each other

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

• All aggregates in one pass

• Each sees only its group

• NULLs follow aggregate rules

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

• The bucket key is the column combination you name

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)

• WHERE happens first

• Form Groups for kept rows

• Aggregates on kept data

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)

• All NULLs = one group

• NULLs match each other here

• Sort puts NULLs at edges

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 → SELECT (SELECT always happens last!)

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

-- Unsafe: song_id is in SELECT but not in GROUP BY
SELECT user_id, song_id, COUNT(*)
FROM Listens
GROUP BY user_id;

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?

The database can't pick, so it errors or silently guesses. Every ? is an ambiguous column.

The fix: group by it

You don't need another table. Add song_id to GROUP BY, and each song 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