CODEWITHSUNDEEP
sqlgroup-bywhere-clause
belfastcowboy24
belfastcowboy24

Reputation: 317

Counting total rows for each group in SQL and rows that meet certain condition

I have a table where information about employees and courses is stored:

Employee    Course    Attends
01          00001     yes
02          00001     no
03          00001     yes
04          00002     yes
05          00002     no

What I'd like to obtain is, for each course, the number of people who is registered and the number of people who has attended, as well as the percentage of attendance for each course. To sum up, my query result would be this way: 

Course    Employees registered    Employees with attendance   Perc. attendance
00001     3                       2                           66
00002     2                       1                           50

How could I achieve this? Thanks!

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can do this with conditional aggregation:

select course, count(*) as num_registered,
       sum(case when attends = 'yes' then 1 else 0 end) as num_attends,
       avg(case when attends = 'yes' then 100.0 else 0 end) as percent_attends
from t
group by course;

Upvotes: 1

Related Questions