Author stats
- SQL course
- Intermediate
- Lesson 86/115
- 5 min
Write an author stats report with CASE and SUM.
Author stats
You can compute post counts per author with CASE and SUM.
SELECT author_id,
SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM posts
GROUP BY author_id;This query gives the published post count per author.
author_id, SUM(CASE WHEN status = 'published' THEN 1 ELSE … is selected from posts. Rows are grouped by author_id; aggregate functions are computed per group.
Author stats — details
You produce 1/0 with CASE and count with SUM. GROUP BY groups per author.
This query gives each author's published post count on one row; the same method can compute drafts in a separate column.
Practice Author stats
Count published posts per author with SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END).
Starter code
-- author stats
SELECT author_id,Sample solution
SELECT author_id,
SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM posts
GROUP BY author_id;Expected output
author_id published 1 5 2 3
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Author stats: match the terms
| Term | Meaning |
|---|---|
SUM(CASE) |
conditional count |
GROUP BY |
group |
published |
published |
author_id |
author |
2 What does conditional counting use?
- SUM + CASE Correct answer
- COUNT + WHERE
- MIN + HAVING
- AVG + GROUP
Explanation: SUM and CASE do conditional counting.
3 Add SUM
SELECT author_id,
____(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM posts
GROUP BY author_id;SELECT author_id,
SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM posts
GROUP BY author_id;Explanation: CASE gives 1 for published posts; SUM counts each author's published posts.