Author stats

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.

Exercise Author stats

Count published posts per author with SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END).

Show example solution

SELECT author_id,
  SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM posts
GROUP BY author_id;

Test yourself

Author stats: match the terms

SUM(CASE)

conditional count
GROUP BY

group
published

published
author_id

author
What does conditional counting use?
- SUM + CASE
- COUNT + WHERE
- MIN + HAVING
- AVG + GROUP
Show answer

SUM + CASE — SUM and CASE do conditional counting.

Add SUM

SELECT author_id,
  ____(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM posts
GROUP BY author_id;

Show answer

SELECT author_id,
  SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) AS published
FROM posts
GROUP BY author_id;

Next lesson: Search index

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies