SQL Select Help

Question

I'm trying to count how many created vs published posts there are per user in my database.

POST ID | USER ID | STATUS 

...and an example would be

User ID 1 has 5 posts (5 distinct post IDs) with 3 STATUS = CREATED and 2 STATUS = PUBLISHED. I want the output to show the following columns

USER  CREATED  PUBLISHED 
----------------------------
 1    3        2

Answer 1

Use:

  SELECT t.user,
         SUM(CASE WHEN t.status = 'CREATED' THEN 1 ELSE 0 END) AS created,
         SUM(CASE WHEN t.status = 'PUBLISHED' THEN 1 ELSE 0 END) AS published
    FROM YOUR_TABLE t
GROUP BY t.user