GROUP BY: Grouping and Aggregating Data

Core Concept

• Example: Count how many songs each user listened to (transforms 9 rows into 3 user groups)

Key Rules

1. Group Formation: Rows with identical values in GROUP BY columns form a group

2. Aggregate Scope: Functions like COUNT() operate within each group

3. Execution Order: FROM → WHERE → GROUP BY → SELECT (SELECT always happens last!)

4. NULL Grouping: NULL values form their own group ⚠️

5. SELECT Restriction: Only GROUP BY columns and aggregates allowed in SELECT (else UNSAFE) ⚠️

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

• Combinations create groups

• Often get one-row groups

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

Common Patterns

• Simple Grouping: GROUP BY user_id creates one group per user

• Multiple Columns: GROUP BY user_id, song_id groups by combinations

• With Aggregates: SELECT user_id, COUNT(*), AVG(rating)

• Filter Groups: Use HAVING to filter after grouping

• All Columns: GROUP BY with all non-aggregate SELECT columns

Common Mistakes

The Non-Grouped Column Error; UNSAFE queries

-- WRONG: song_id not in GROUP BY (unsafe query)
SELECT user_id, song_id, COUNT(*)
FROM Listens
GROUP BY user_id

-- CORRECT: Include all non-aggregate columns in GROUP BY
SELECT user_id, song_id, COUNT(*)
FROM Listens
GROUP BY user_id, song_id

Problem: Which song_id to show when user has multiple songs?

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