CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
ExiRe
ExiRe

Reputation: 4767

How can I count field value based on another field?

I have EmailEvent table. This is STI table which stores opens and clicks (by using type attribute). I have right now this code which provides information on how many emails use recipient address (email_address field):

EmailEvent.where("type = 'OpenEmailEvent' OR type = 'ClickEmailEvent'")
.where(email_id: email_ids).happened_between_dates(start_date, end_date)
.select("email_address, count(distinct email_id) as emails_count")
.group(:email_address).reorder(nil)

It generates such code:

SELECT email_address, count(distinct email_id) as emails_count 
FROM "email_events" 
WHERE "email_events"."deleted_at" IS NULL 
    AND (type = 'OpenEmailEvent' OR type = 'ClickEmailEvent') 
    AND "email_events"."email_id" IN (85487, 75554, 85489, 77184, 78562, 75587, 82879, 85535, 85534) 
    AND (created_at between '2017-02-28 22:52:01.000000' AND '2017-03-29 05:59:59.000000') 
GROUP BY "email_events"."email_address"

I need to provide information on how many opens and clicks there per email_address. I can't use count(id) because it'll give me both opens and clicks so I need to use type attribute somehow but I can't figure out exactly how it should work. Can somebody give me suggestion what should I do here?

Upvotes: 0

Views: 32

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

replace this

count(distinct email_id) as emails_count

to something like in SQLServer using case or IIF

sum(case when type = 'OpenEmailEvent' then 1 else 0 end)  [Opens],
sum(case when type = 'ClickEmailEvent' then 1 else 0 end)  [Click],

Upvotes: 2

Related Questions