Maksim Borodov Maksim Borodov -4 years ago 77
MySQL Question

Calculate count records from mysql group by fields

My table in MySQL

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

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( 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 = 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