Mysql – How to generate missing values for each employee for each day

group byjoin;MySQL

CREATE TABLE `EmpWorkCount` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `day` int(10) unsigned NOT NUL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `daycount` int(10) NOT NULL DEFAULT '0',
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Current Values in the table :

    emp_Id    |       day            | daycount  |
       1      |        0             |  10       |
       1      |        1             |  11       | 
       2      |        1             |  13       | 
       2      |        0             |  12       | 

.

CREATE TABLE `Emp` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Emp Table looks like :

emp_Id    |       created_At            | updated_At            |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  | 
   3      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |

I need to run a query where I need a count of EmpWorkCount with different day values (0, 1, 2, 3) for each employee

    Select emp_Id, day_list.day, 
    IF(daycount = NULL, 0, daycount)  as daycount from (
        SELECT 0 day
           UNION ALL
           SELECT 1
           UNION ALL
           SELECT 2
           UNION ALL
           SELECT 3   )day_list 
      LEFT JOIN 
      select emp_Id, day, daycount from EmpWorkCount
   )   

(my query is incorrect, but I don't know how I multiply these 4 values from day for each employee

this gives output for the emp_Id, day values present in the table e.g.

emp_Id    |       day            | daycount    |
   1      |        0             |  10         |
   1      |        1             |  11         | 
   2      |        1             |  13         |
   2      |        0             |  12         | 
   NULL   |        2             |  0          | 
   NULL   |        3             |  0          | 

but if the database does not contain let's say emp_Id : 1 with day values = 2 or 3 these records don't appear with a count 0.

Output that I look for :

    emp_Id    |       day            | daycount    |
       1      |        0             |  10         |
       1      |        1             |  11         | 
       1      |        2             |  0          |
       1      |        3             |  0          | 
       2      |        1             |  13         |
       2      |        0             |  12         | 
       2      |        2             |  0          | 
       2      |        3             |  0          | 
       3      |        0             |  0          |
       3      |        1             |  0          | 
       3      |        2             |  0          | 
       3      |        3             |  0          | 

How do I go about this one?

In my query I have not joined with Employee Table –> but the output should even contain Employees which do not have a entry in EmpWorkCount Table.

Best Answer

You must generate all possible (employee, day) pairs, so you must obtain employees list additionally, not only days list.

The query will be something like

SELECT emp_list.emp_Id, day_list.day, COALESCE(EmpWorkCount.daycount, 0) daycount
FROM ( SELECT 0 day
       UNION ALL
       SELECT 1
       UNION ALL
       SELECT 2
       UNION ALL
       SELECT 3 ) day_list 
JOIN ( SELECT emp_Id FROM EmpWorkCount 
       UNION 
       SELECT emp_Id FROM Emp ) emp_list
LEFT JOIN EmpWorkCount ON day_list.day = EmpWorkCount.day
                      AND emp_list.emp_Id = EmpWorkCount.emp_Id
/* ORDER BY 1, 2 */