Count Rows On a Condition in SQL SERVER 2000

Question

I want to fetch data in a following format from SQL SERVER 2000

Department | TotalPresent | Employee | Officer
XYZ              12           6          6

I am running this query

SELECT     a.department,
           Count(emp_id) AS totalpresent,
           CASE
                      WHEN a.type = 'Employee' THEN Count(a.type)
                      ELSE 0
           END AS employee,
           CASE
                      WHEN a.type = 'Officer' THEN Count(a.type)
                      ELSE 0
           END AS officer
FROM       attendancelog m
INNER JOIN employeedata a
ON         m.emp_id = a.emp_id groupby a.type,
           a.department

when i run this query i get this sort of data which is not correct

Department | TotalPresent | Employee | Officer
    XYZ              12           6         0
    XYZ              12           0         6

Please correct me what seems to be the problem why is it returning me the same records twice but fulfilling the condition

All i want to is count the rows on the condition whether the type is employee or officer

Answer 1

Remove the type from the group by and move the case inside the sum():

Select a.department, count(emp_id) as TotalPresent,
       sum(case when a.Type = 'Employee' THEN 1 ELSE 0 END) as Employee,
       sum(case when a.Type = 'Officer' THEN 1 ELSE 0 END) as Officer
from AttendanceLog m INNER JOIN
     EmployeeData a
     ON m.emp_id = a.emp_id
GroupBy a.Department;

Also, upgrade your SQL Server. SQL Server 2000 has been unsupported for years. You should try to use supported software.