user3206440 user3206440 - 1 month ago 6
SQL Question

SQL - condtion in aggregate function while grouping

table1



person | zipcode | timestamp | event | device
--------------------------------------------------|--------|------
amar | 11111 | 2016-09-28 20:05:03.001 | email | phone
akbar | 11111 | 2016-09-28 20:05:03.001 | email | phone
antony | 11111 | 2016-09-28 20:07:03.001 | chat | pc
amar | 11111 | 2016-09-28 20:08:03.001 | email | phone
amar | 11111 | 2016-09-28 20:08:03.001 | chat | phone
amar | 22222 | 2016-09-28 20:09:03.001 | email | phone
akbar | 22222 | 2016-09-28 20:10:03.001 | email | phone
antony | 22222 | 2016-09-28 20:10:03.001 | chat | phone
amar | 11111 | 2016-09-28 21:05:03.001 | email | phone
akbar | 11111 | 2016-09-28 21:05:03.001 | email | phone
antony | 11111 | 2016-09-28 21:07:03.001 | chat | phone


output desired

person | total_events | email_events
---------------------------------------
amar | 5 | 4
akbar | 3 | 3
antony | 3 | 0


Thinking of using
group by
for this like below - how to get count of events where
event = 'email'
here - is there a way to do this without using a sub-select?

select
person
, count (*) as total_events
from table1
group by person

Answer

Use group by along with case:

select person,
       count(*) as total_events,
       sum(case when event = 'email' then 1 else 0 end) as email_events
from table1
group by person;

In Postgres, you can shorten this to:

select person,
       count(*) as total_events,
       sum( (event = 'email')::int ) as email_events
from table1
group by person;
Comments