SQL - condtion in aggregate function while grouping

Question

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

... count(*) filter (where event = 'email') as email_events — Abelisto
– Abelisto, Commented Oct 22, 2016 at 14:17

Pரதீப் · Accepted Answer · 2016-10-22 13:51:36Z

3

Add the condition in CASE statement and wrap it in COUNT aggregate

Try this

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

answered Oct 22, 2016 at 13:51

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abelisto Over a year ago

BTW simpler a bit: ..., count(nullif(event = 'email', false)) ...

Gordon Linoff · Accepted Answer · 2016-10-22 13:51:48Z

2

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;

answered Oct 22, 2016 at 13:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL - condtion in aggregate function while grouping

table1

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

table1

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related