CODEWITHSUNDEEP
mysqlsql
user3258571
user3258571

Reputation: 384

Counting twice within one query

What am I doing wrong? Trying to output two COUNTs and need to Group by Department. 

SELECT Department, 
       COUNT(DISTINCT ID) AS total, 
       SUM(CASE WHEN course LIKE 'AS%' THEN 1 
                ELSE 0 
           END) AS Total_AS
FROM schedule 
GROUP BY Department 
ORDER BY Department ASC

Only problem is that Total_As is counting Department for multiple instances. Just need to count once if employee(ID) took at least one training with Course AS%. The script is currently counting every single AS% taken by employee(ID). Need the case part to only count the employee(ID) once to indicate that employee took at least one AS% training or 0 if they did not attend. SUM should not count the employee more than once. Tried to use MAX in place of SUM but the results only gave 1 or 0 per Department. Trying to get output of Department, Total Employees, # of Employees in Department that have at least 1 Course Like 'AS%'.

Output should be:

Accounting, Total of 20 employees in Department, Total_AS 10.

Using current SUM, I get a result for Total_AS of 40 because 10 of the Employees(ID) took 4 courses each. Just need Total_AS to be COUNT of Employees who took at least 1 Course, which in this case would be 10.

Upvotes: 1

Views: 1867

Answers (1)

dnoeth
dnoeth

Reputation: 60472

Change the SUM to a COUNT(DISTINCT):

SELECT Department,
   COUNT(DISTINCT ID) AS total, 
   COUNT(DISTINCT CASE WHEN course LIKE 'AS%' THEN id END) Total_AS 
FROM schedule 
GROUP BY Department 
ORDER BY Department ASC

Upvotes: 2

Related Questions