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