CODEWITHSUNDEEP
sqlsqlitejoin
Bijan
Bijan

Reputation: 8586

SQLite Group Multiple Sub Queries by ID

I have a table in my database that has following format

author | status
---------------
Michael | New
Michael | New
Kevin | Repost
Kevin | New
Michael | Repost
Steve | New

How would I construct a query that counts the # of status' that are New and Repost and puts them next to the author name

i.e. the following query would output

author | # new | # repost
-------------------------
Michael | 2 | 1
Kevin | 1 | 1
Steve | 1 | 0

The problem I have is that if there is X of one type of post but 0 of the other, the entire row is disregarded.

Here is the query I have thus far:

SELECT New.post_author, Newc, Repostc from (
SELECT post_author, count(*) as 'Newc' FROM flair WHERE status = 'New' GROUP BY post_author) as New
inner join (
SELECT post_author, count(*) as 'Repostc' FROM flair WHERE status='Repost' GROUP BY post_author) as Repost 
on New.post_author = Repost.post_author

except for the following query it would output:

author | # new | # repost
-------------------------
Michael | 2 | 1
Kevin | 1 | 1

completely disregarding Steve

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Just use conditional aggregation:

select post_author,
       sum(case when status = 'New' then 1 else 0 end) as newposts,
       sum(case when status = 'Repost' then 1 else 0 end) as reposts
from flair f
group by post_author;

Upvotes: 2

Related Questions