MySQL Query Issue

Question

I have the current database setup:

Forename | Surname | Department | Completion_Status
Tom        Smith     Sales        Started
Bob        Jones     Sales        Completed
Alison     Baines    Sales        Not Started
Arthur     Smith     Marketing    Started
Claire     Staines   Marketing    Completed

I am able to return the total number of rows without any problems and the total number where completion status is either Started or Completed but not in a single statement - however what I would like to do, in a single statement, is:

COUNT for Department As Total AND the COUNT for Department As Responses WHERE Completion_Status IN ('Started', 'Completed')

It would look something like this:

Department | Total | Responses
Sales        3       2
Marketing    2       2

Hope that makes sense!?

Thanks Homer.

Answer 1

select 
department,
count(department) as total,
sum(if(completion_status  IN ('Started', 'Completed'),1,0)) as responses
from table
group by department

This is a more standard version

select 
department,
count(department) as total,
sum(case when completion_status in ('Started', 'Completed') then 1 else 0 end ) as responses
from table
group by department