MySQL: query should return rows with count 0

Question

I have 2 tables - User and Department and query that returns a count of users by departments. If user don't have department that returns "No department". But I needs to get also departments without user as count 0. This is my query:

SELECT COALESCE(departments.name, 'No department') AS name, count( * ) AS count
FROM users
LEFT JOIN departments ON departments.id = users.department_id
WHERE users.is_deleted = 0
AND users.company_id = 1
AND (TIMESTAMPDIFF(YEAR, `date_of_birth`, CURDATE()) BETWEEN 1 AND 18 )
GROUP BY departments.name

It should like this:

________________________
  Dep name  |  count    |
________________________
Dep 1       | 2         |
________________________
Dep 2       | 3         |
________________________
Dep 3       | 0         | if users  in this department not exist
________________________
No dep      | 1         | if users not have department
________________________

Help me, please, guys!

I found the solution

SELECT   COALESCE(locations.name, 'Without location')  AS location,
            COUNT(IF(TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 1 AND 17, 1, NULL)) 'group_1_17'

          FROM users
          LEFT JOIN locations ON locations.id = users.location_id
          WHERE users.is_deleted = 0 AND users.company_id = :company_id
          GROUP BY locations.name

          UNION

          SELECT
            locations.name AS location,
            0 'group_1_17'         
          FROM users
          RIGHT JOIN locations ON locations.id = users.location_id
          WHERE locations.company_id = :company_id AND users.id IS NULL"

In that case, consider providing proper DDLs and/or an sqlfiddle TOGETHER WITH THE DESIRED result set. My guess is that @ohmygirl is on the right track, but it's normally better (in the sense of 'more comprehensible') to just rewrite the query with a LEFT JOIN. — Strawberry
– Strawberry, Commented Oct 21, 2013 at 15:06
COUNT departments.id - not for my case. because it should return count users without depart. — Mary Dear
– Mary Dear, Commented Oct 21, 2013 at 15:38

PaulProgrammer · Accepted Answer · 2013-10-21 14:53:25Z

3

having count(*) = 0

This is the "where" for aggregates.

SELECT COALESCE(departments.name, 'No department') AS name, count( * ) AS count
FROM users
LEFT JOIN departments ON departments.id = users.department_id
WHERE users.is_deleted = 0
AND users.company_id = 1
AND (TIMESTAMPDIFF(YEAR, `date_of_birth`, CURDATE()) BETWEEN 1 AND 18 )
GROUP BY departments.name
HAVING COUNT(*) = 0

answered Oct 21, 2013 at 14:53

PaulProgrammer

18k4 gold badges46 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mary Dear Over a year ago

It not help .MySQL returned an empty result set . But should not

PaulProgrammer Over a year ago

Can you use sqlfiddle.com to build a sample data set?

Teja · Accepted Answer · 2013-10-21 15:02:53Z

0

SELECT CASE WHEN COUNT_DEPTS=0
            THEN 'No department'
            ELSE NAME
        END ,
        COUNT_DEPTS  
FROM
(
    SELECT
       D..NAME,COUNT(D.ID) COUNT_DEPTS
      FROM USERS U
      LEFT OUTER JOIN 
           DEPARTMENTS D 
      ON U.DEPARTMENT_ID = D.ID
     WHERE U.IS_DELETED = 0
       AND U.COMPANY_ID = 1
       AND (TIMESTAMPDIFF(YEAR, `date_of_birth`, CURDATE()) BETWEEN 1 AND 18 )
    GROUP BY D.NAME
);

answered Oct 21, 2013 at 15:02

Teja

13.7k38 gold badges103 silver badges164 bronze badges

2 Comments

Mary Dear Over a year ago

No-no-no! I needs to get also departments without user as count 0! No Department if If user don't have department

PaulProgrammer Over a year ago

Subselects scale very badly in mysql if the data set is much larger than a toy.

geomagas · Accepted Answer · 2013-10-21 17:42:43Z

From what I understand, you need both users without a department and departments with no users. Your left join will give you the latter, but not the former. In order to get both, you need a full join. But MySQL doesn't support full joins. And even if it did, the conditions in your where clause would eliminate the "right" part of the full join.

So the only way I can think of, besides emulating a full join, is to "manually" append the non-populated departments, with a zero count, using a union:

SELECT COALESCE(departments.name, 'No department') AS name, count( * ) AS count
FROM users
LEFT JOIN departments ON departments.id = users.department_id
WHERE users.is_deleted = 0
AND users.company_id = 1
AND (TIMESTAMPDIFF(YEAR, `date_of_birth`, CURDATE()) BETWEEN 1 AND 18 )
GROUP BY departments.name
UNION
SELECT name,0 FROM departments
WHERE id NOT IN (
  SELECT department_id FROM users
  WHERE department_id IS NOT NULL
  )

Not so elegant, granted, but it does the dirty job...

Collectives™ on Stack Overflow

MySQL: query should return rows with count 0

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related