CODEWITHSUNDEEP
mysql
Fawzan
Fawzan

Reputation: 4849

Join two tables and group by two columns

I have two MySQL tables.

User Table

id | country

Post Table

id | userId | type

in the Post table I have 3 types of posts. 

'Social', 'Economic', 'Culture'

I need to group the data in order to obtain the count of posts per type.

eg. 

country | social  | economic | culture
UK      | 2       | 1        | 0       
US      | 0       | 2        | 1       
LK      | 1       | 0        | 0       
IN      | 0       | 0        | 3

I wrote the following query which seems to group the data only using the country. How can I port this such that it counts the type of post?

SELECT b.country, SUM(a.type) 
FROM Post a 
INNER JOIN User b ON a.id = b.id 
GROUP BY b.country, a.type

Can anyone give me pointer here?

Upvotes: 0

Views: 51

Answers (1)

juergen d
juergen d

Reputation: 204746

SELECT b.country, 
       SUM(a.type = 'Social') as social,
       SUM(a.type = 'Economic') as economic,
       SUM(a.type = 'Culture') as culture
FROM Post a 
INNER JOIN User b ON a.id = b.id 
GROUP BY b.country

The sum() adds up how many time the condition is true.
Only group by data you want to have its own row in the result set.

Upvotes: 1

Related Questions