Group By and Aggregates

Introduction

Aggregate functions summarize many rows—counts, sums, averages. GROUP BY splits data into buckets before aggregating; HAVING filters those buckets. This chapter builds reporting queries on users and posts, compares WHERE vs HAVING, and previews WITH ROLLUP subtotals.

Prerequisites

• SELECT Queries Basics
• Joins and Multi-Table Queries
• Sample rows in blog_dev

Aggregate Functions

Whole table:

SELECT COUNT(*) AS total_posts FROM posts;
 
SELECT
  COUNT(*) AS all_posts,
  COUNT(published_at) AS published_posts,
  SUM(CASE WHEN published_at IS NULL THEN 1 ELSE 0 END) AS drafts
FROM posts;

Code explanation:

• COUNT(*) counts rows
• COUNT(col) ignores NULL in col

Other aggregates:

SELECT
  MIN(published_at) AS first_publish,
  MAX(published_at) AS last_publish
FROM posts
WHERE published_at IS NOT NULL;

AVG, SUM apply to numeric columns—use DECIMAL for money.

GROUP BY

Posts per user:

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id;

With author name via join:

SELECT
  u.display_name,
  COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.display_name;

Code explanation:

• Every non-aggregated SELECT column must appear in GROUP BY (MySQL ONLY_FULL_GROUP_BY mode enforces sensibly)

COUNT DISTINCT

Unique authors who published:

SELECT COUNT(DISTINCT user_id) AS publishing_users
FROM posts
WHERE published_at IS NOT NULL;

HAVING vs WHERE

WHERE filters rows before grouping:

SELECT user_id, COUNT(*) AS published_count
FROM posts
WHERE published_at IS NOT NULL
GROUP BY user_id;

HAVING filters groups after aggregation:

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING post_count >= 2;

Authors with at least two published posts:

SELECT u.display_name, COUNT(p.id) AS published_count
FROM users u
INNER JOIN posts p ON p.user_id = u.id
WHERE p.published_at IS NOT NULL
GROUP BY u.id, u.display_name
HAVING published_count >= 2;

ORDER BY With Aggregates

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC, user_id ASC;

Top poster:

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 1;

WITH ROLLUP (Subtotals)

Grand total row appended:

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id WITH ROLLUP;

Last row shows NULL user_id with total count across groups.

Multi-column rollup (MySQL extends grouping sets):

SELECT user_id, DATE(published_at) AS pub_day, COUNT(*) AS c
FROM posts
WHERE published_at IS NOT NULL
GROUP BY user_id, DATE(published_at) WITH ROLLUP;

Use for report subtotals—interpret NULL keys as summary levels.

Common Mistakes

-- Invalid under ONLY_FULL_GROUP_BY:
-- SELECT user_id, title, COUNT(*) FROM posts GROUP BY user_id;

Practice Reports

1. List each user and count of drafts vs published (hint: conditional SUM)
2. Month of publish and post count (DATE_FORMAT)
3. Users with zero posts (LEFT JOIN + HAVING post_count = 0 or NOT EXISTS)

Example month bucket:

SELECT
  DATE_FORMAT(published_at, '%Y-%m') AS month,
  COUNT(*) AS posts_in_month
FROM posts
WHERE published_at IS NOT NULL
GROUP BY DATE_FORMAT(published_at, '%Y-%m')
ORDER BY month;

FAQ

GROUP BY every SELECT column?

All non-aggregated selected columns must be in GROUP BY list or functionally dependent (MySQL 8+ rules).

HAVING without GROUP BY?

Treats whole table as one group—allowed but rare.

AVG of integers?

MySQL may return decimal—cast if needed.

Rollup vs UNION totals?

ROLLUP one query; UNION manual total row—pick clarity.

Window functions instead?

ROW_NUMBER, SUM() OVER—chapter 26; replace some GROUP BY patterns.

Performance?

Index columns in WHERE and JOIN; aggregates still scan matching rows—large tables need tuning chapter.