Conditional Agregation for numbered states

user_12949981 0
Viewed 33

I'm having a hard time wrapping my head around a SQL Query. I'm thinking that somehow conditional agregation can be used, but here is my situation:

I have the following table:

+------------+----------+--------------+-------------+------------------+
| Email_Name | Email_Id | Subject_Line | Customer_Id | Interaction_Type |
+------------+----------+--------------+-------------+------------------+
| Email1     |        1 | Hello_1      |           1 | SENT             |
| Email2     |        1 | Hello_1      |           2 | SENT             |
| Email3     |        1 | Hello_1      |           3 | OPEN             |
| Email4     |        1 | Hello_1      |           4 | OPEN             |
| Email5     |        1 | Hello_1      |           5 | CLICK            |
| Email6     |        1 | Hello_1      |           6 | SENT             |
+------------+----------+--------------+-------------+------------------+

Usually, the input data is ordered by customer ID, for example a email sent to 100 people will show up a 100 times (per customer id). If 50 emails are opened, I will have an additional 50 lines of the email with OPEN state and so on.

What I want to do is aggregate the data so I have a single line for each Email ID followed by 3 columns OPEN/SENT/CLICK with numbers saying how many times it was sent/opened/clicked.

Can it be done? Using MySql. Thank you kindly.

pivot group-by mysql sql database
2 Answers

Conditional aggregation:

select email_id, 
       sum(Interaction_Type = 'SENT') as num_sent,
       sum(Interaction_Type = 'OPEN') as num_open,
       sum(Interaction_Type = 'CLICK') as num_click
from t
group by email_id;
user_11440350

You do want conditional aggregation.

This works by grouping the dataset by email_name, and then performing conditional sums for each of the interaction_types, like so:

select 
    email_name,
    sum(interaction_type = 'SENT') sent,
    sum(interaction_type = 'OPEN') open,
    sum(interaction_type = 'CLICK') click
from mytable
group by email_name

In MySQL, conditional expressions, when evaluated in numeric context, yield 1 when fulfilled and 0 when not. So an expression like sum(interaction_type = 'SENT') increases by 1 for each record where interaction_type = 'SENT'.

user_106767160
Related