I have a table named Employees with below two columns.
1- EmpId (auto incremental id)
2- EmpName (varchar)
There is another table named 'LateDays' with below five columns.
1- LateDayId (auto incremental id)
2- EmployeeId (foreign key)
3- LateDate (DateTime)
4- FinePaid (bool)
5- Informed (bool)
Now I am trying to write a SQL statement to fetch employees along with their Total Fine, Paid Fine and their Allowed Quota Availed. Each employee has 4 allowed days to come late. I came up with the following (incorrect) SQl, which works fine when there is only one employee in Employees table but fails for more.
SELECT Employees.name as EmpName, (select count(LateDays.LateDate)*200 from Employees inner join LateDays on Employees.EmpId = LateDays.EmployeeEmpId) as TotalFine, count(LateDays.LateDate)*200 as PaidFine, (select COUNT(LateDays.Informed)from Employees inner join LateDays on Employees.EmpId = LateDays.EmployeeEmpId where LateDays.Informed='true') as InformedDays FROM Employees inner join LateDays on Employees.EmpId = LateDays.EmployeeEmpId WHERE LateDays.FinePaid = 1 GROUP BY name
Kindly help me writing a proper SQL for my situation.
Edit: I am trying to add sample data.
EmpId Name 1 Pervaiz 2 Nabeel 3 Muhammad 4 james
Late Days table:
LateDayId EmployeeEmpId LateDate FinePaid Informed 4 1 2014-09-21 0 0 5 1 2014-09-20 0 1 6 6 2014-09-21 0 0 7 6 2014-10-01 1 0