Summerize sql count on post statuses

Question

I have a the following table structure:

Users table:

id       username                  
1           bob            
2           john             
3           harry

Posts table:

id       user_id          status        
1           1             4 
2           2             4    
3           3             2        
4           1             1      
5           2             1

If I wanted a summary of posts per user and cols of statuses, what is the most efficient sql to generate that? Ex:

username       status 4  status 2 status 1  
bob             1         0        1
john            1         0        1  
harry           0         1        0

possible duplicate of [MySQL - How to display row value as column name using concat and group\_concat](http://stackoverflow.com/questions/13985470/mysql-how-to-display-row-value-as-column-name-using-concat-and-group-concat) — R D, Jul 03 '14 at 10:14

score 0 · Accepted Answer · answered Jul 03 '14 at 08:31

0

This should work:

SELECT users.username
, sum(if(posts.status=4,1,0)) as `status 4`
, sum(if(posts.status=2,1,0)) as `status 2`
, sum(if(posts.status=1,1,0)) as `status 1`
 FROM users, posts
 WHERE users.id=posts.user_id
 GROUP BY users.id

answered Jul 03 '14 at 08:31

hellcode

2,678
1
17
21

score 0 · Answer 2 · answered Jul 03 '14 at 08:35

You can try somthing like this:-

SELECT A.USERNAME, 
CASE WHEN STATUS = 4 THEN COUNT(B.STATUS) AS STATUS4 ELSE 0 END,
CASE WHEN STATUS = 2 THEN COUNT(B.STATUS) AS STATUS2 ELSE 0 END,
CASE WHEN STATUS = 1 THEN COUNT(B.STATUS) AS STATUS1 ELSE 0 END,
FROM USERS INNER JOIN POSTS
ON USERS.ID = POSTS.USER_ID
GROUP BY USERS.ID;

This might help you.

Summerize sql count on post statuses

2 Answers2