mysql counts all identifiers given in the where clause and counts the rest of the others

advertisements

I want a list where i can get count of my given id's in where clause, and the count of remaining id's as rest here is my query:

Select cat.name AS name, COUNT(cat.name) AS total
FROM MyAppBundle:Content c INNER JOIN c.category cat
WHERE cat.id IN(11,7,16,1,12,13)
GROUP BY cat.name;

but dont know how to get the rest total, can anyone help?


You can combine SUM and CASE to get both the matching and non-matching counts:

SELECT cat.name AS name,
    SUM(CASE WHEN ID IN (11,7,16,1,12,13) THEN 1 ELSE 0) AS countMatch,
    SUM(CASE WHEN ID NOT IN (11,7,16,1,12,13) THEN 1 ELSE 0) AS countNotMatch
FROM MyAppBundle:Content c INNER JOIN c.category cat
GROUP BY cat.name