Sql-server – how to group data against more than one conditions

sql-server-2008

I have a table named Employees with below two columns.

==================

Employees

1- EmpId (auto incremental id)

2- EmpName (varchar)

There is another table named 'LateDays' with below five columns.

==================

LateDays

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.

Employees table:

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

Best Answer

The reason this isn't working for you when you have multiple rows in the employee table is that it is not correlated back to the main query. By this I mean that SQL has no way of knowing which employee to count in your subqueries for a given row in your main query. You can avoid this buy adding a reference back to your main query like this:

SELECT 
    Employees.name as EmpName,
    (SELECT COUNT(LateDays.LateDate)*200 
        FROM Employees E
        INNER JOIN LateDays LD 
            ON E.EmpId = LD.EmployeeEmpId
        WHERE E.EmpId = Employees.EmpId) 
    as TotalFine,

Notice how I aliased the tables in the subquery so they had a different name from those in the outer query. Then in the WHERE clause I referenced the table name from the outer query.

However this is not how I would write your query. It will probably be less than well performing. Instead you can use a pattern where you use a CASE statement to define your condition returning a 1 or a 0 then SUM the values.

The final query looks like this:

SELECT
    Employees.name AS EmpName,
    COUNT(LateDays.LateDate)*200 AS TotalFine,
    SUM(CASE WHEN LateDays.FinePaid = 1 AND LateDays.LateDate IS NOT NULL THEN 1 ELSE 0 END) AS PaidFine,
    SUM(CASE WHEN LateDays.Informed = 1 THEN 1 ELSE 0 END) AS InformedDays
FROM Employees 
INNER JOIN LateDays
    ON Employees.EmpId = LateDays.EmployeeEmpId
GROUP BY Employees.EmpId, Employees.Name

A couple of things of note. I am checking that LateDate is not NULL in the first SUM to match your COUNT. COUNT doesn't actually count a value if it is NULL. Second is that I changed the GROUP by to Employees.EmpId. You generally do not want to GROUP BY a NAME column. What happens when two employees have the same name? It's rare but it does happen. You are better off grouping on the ID column.

EDIT:

Based on having test data I made a few corrections to the query and have a question.

Change1: I had missed a comma in the SELECT list.

Change2: I had forgotten to add Employees.Name to the GROUP BY. You should still have Employes.EmpId to ensure grouping by distinct employees but because you are pulling Employees.Name you do have to include it.

Question: In your test data rows 6 & 7 have the same date but the same EmployeeEmpId. Should this be counted as two for the TotalFine calculation?

Note: Rows 6 & 7 of your test data have an EmployeeEmpId of 6 but that Id doesn't exist in your Employees table test data. I changed it to a 3 for my testing.

Related Question