Maksim Borodov Maksim Borodov -4 years ago 77
MySQL Question

Calculate count records from mysql group by fields

My table in MySQL
http://joxi.ru/5mdWRV8tyQzyr1

My programm pass array users

$ids = [1, 3, 7];


My query for table:

SELECT responsible_id, count(id) as count
from test
WHERE active = 1
AND status = 3
AND responsible_id in (1, 3, 7)
GROUP BY responsible_id
ORDER BY count(id)


I get result http://joxi.ru/vAWYGq0IMxdjmW

But, I need also first row with responsible_id = 7 and count = 0 if not exist on table.

Answer Source

To do what you want, use a left join:

SELECT v.responsible_id, count(t.id) as count
FROM (SELECT 1 as responsible_id UNION ALL
      SELECT 3 as responsible_id UNION ALL
      SELECT 7 as responsible_id
     ) v LEFT JOIN
     test t
     ON t.responsible_id = v.responsible_id AND
        t.active = 1 AND
        t.status = 3
GROUP BY v.responsible_id
ORDER BY count(id);

Note that the conditions in the WHERE have been moved to the ON clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download