CODEWITHSUNDEEP
mysqljoincountouter-join
Danny
Danny

Reputation: 1005

Join two queries with COUNT from the same table in MySQL

I have two queries that I run in the same table:

SELECT id, COUNT(up) 
FROM comentarios 
WHERE up = 1
GROUP BY id

And

SELECT id, COUNT(down) 
FROM comentarios 
WHERE down = 2
GROUP BY id

I tried something like this but doesn't work

SELECT t1.id, COUNT(t1.up), t2.id, COUNT(t2.down)
FROM (SELECT id, up FROM comentarios WHERE up = 1 GROUP BY id) t1
JOIN (SELECT id, down FROM comentarios WHERE down = 2 GROUP BY id) t2

ON t1.id = t2.id

Maybe a need a FULL OUTER JOIN?

What's the best way to do this in MySQL?

Upvotes: 0

Views: 1904

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

SELECT id,
       SUM(CASE WHEN up = 1 THEN 1 ELSE 0 END) AS UpCount,
       SUM(CASE WHEN down = 2 THEN 1 ELSE 0 END) AS DownCount
    FROM comentarios
    GROUP BY id

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

select id, 
    sum(case when up = 1 then 1 end) as UpCount,
    sum(case when down = 2 then 1 end) as DownCount
from comentarios  
group by id

Upvotes: 1

Related Questions