CODEWITHSUNDEEP
mysql
user460114
user460114

Reputation: 1848

Aggregate ad clicks and views

Given the following table, which tracks clicks and views per ad campaign:

record_id     advert_id     type
-----------------------------------
1             100           click
2             102           click
3             100           view
4             100           view
5             102           view
6             100           view
7             101           view
8             101           click

Is it possible to aggregate the clicks and views with a single query, i.e. so that it looks like this:

advert_id     clicks      views
-------------------------------
100           1           3
101           1           1
102           1           1

Upvotes: 0

Views: 102

Answers (2)

juergen d
juergen d

Reputation: 204756

Group by advert_id and use a conditional aggregation

select advert_id, 
       sum(type = 'click') as clicks,
       sum(type = 'view') as views
from your_table
group by advert_id

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • We can GROUP BY on advert_id. This will allow you to aggregate results for a specific advert_id into a single row.
  • Now, we can utilize Case .. When expression on type to conditionally check whether its value is click or type and accordingly Count() the clicks, and views respectively.

Try the following: 

SELECT 
  advert_id
  COUNT(CASE WHEN type = 'click' THEN 1 END) AS clicks, 
  COUNT(CASE WHEN type = 'view' THEN 1 END) AS views 
FROM  your_table 
GROUP BY advert_id

Upvotes: 1

Related Questions