Question 342505 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL: Display the total number of dependents for each employee for employees who have at most 1 dependents

So I have to execute this query in MYSQL

Display the total number of dependents for each employee for employees who have at most 1 dependents

which is easy enough but the problem I’m facing is that some of the values in the DEPENDENT_ID column of the DEPENDENT table are zero which MYSQL is also including in the COUNT(DEPENDENT_ID) function. I can’t even change it to NULL to make my code work (by using IFNULL) since the DEPENDENT_ID is a PRIMARY KEY along with EMPID. PRIMARY KEY (EMPID, DEPENDENT_ID). Here is the data for your reference:

DEPENDENT table:

EMPID DEPENDENT_ID
101 302
102 301
103 302
104 0
105 0

DESCRIBE DEPENDENT table:

FIELD TYPE NULL KEY DEFAULT EXTRA
EMPID INT NO PRI NULL
DEPENDENT_ID INT NO PRI NULL

So far, I’ve done this which is not the desired result.

SELECT EMPID, COUNT(DEPENDENT_ID)
FROM DEPENDENT
GROUP BY EMPID
HAVING COUNT(DEPENDENT_ID)<=1;
EMPID COUNT(DEPENDENT_ID)
101 1
102 1
103 1
104 1
105 1

I really don’t know how to make the ones against EMPID 104 and 105 change to zeros because these employees technically have no dependents. In the DEPENDENT table the DEPENDENT_ID of 104 and 105 show up as ZERO because I had to insert some value as the column is NOT NULL. So I put zero instead of any specific ID (like 302, 303, etc).

I’m expecting the result to be like this:

EMPID COUNT(DEPENDENT_ID)
101 1
102 1
103 1
104 0
105 0

I’ve tried a lot of techniques but they all result in some kind of syntax error and Google couldn’t help as well. Help is much appreciated.

Tags:

2

Answers


  1. 0

    Conditionally sum

    SELECT EMPID, SUM(CASE WHEN DEPENDENT_ID > 0 THEN 1 ELSE 0 END)
FROM DEPENDENT
GROUP BY EMPID
HAVING COUNT(DEPENDENT_ID)<=1;

    https://dbfiddle.uk/xn4Zbu7z

    Login or Signup to reply.
  2. 0

    Here’s a solution:

    SELECT EMPID, COUNT(NULLIF(DEPENDENT_ID, 0)) AS real_count
FROM DEPENDENT
GROUP BY EMPID
HAVING real_count<=1;

    COUNT() ignores NULLs. I know you are storing 0 which signifies "no dependent" and can’t change this in the table.

    You can make an expression with the NULLIF() function that substitutes NULL for 0 as it counts them.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search