user3499878 user3499878 - 6 months ago 8
SQL Question

Count different rows from one table

I have table

t1
:

id|id_title|action


The data in table:

1|1|like
2|1|like
3|1|unlike
4|2|share
5|2|share


So I want to get next result from query:

id|count like|count unlike|count share

1|2|1|0
2|0|0|2


I try to use next query:

SELECT id_title, ( Select Count(id) From T1 WHERE action='like') As CountOfItems FROM T1 GROUP BY id_title


But it return count of first row always. What I must do? Or maybe I must changed structure of table?

Answer
SELECT id_title AS id, SUM(action='like') AS 'count like', 
SUM(action='unlike') AS 'count unlike', SUM(action='share') 
AS 'count share' FROM t1 GROUP BY id_title

Let me know if that worked for you.