Count of data from one column into multiple columns with MySQL?

Question

I am having trouble understanding how to split and count different activities into separate columns. This is the beginning table:

+------------+---------+
| CustomerID |Activity |
+------------+---------+
|          1 | Click   |
|          1 | View    |
|          1 | Inquiry |
|          2 | Click   |
|          2 | View    |
|          3 | Click   |
|          3 | Click   |
+------------+---------+

I'd like to be able to transform it to this:

+------------+------+-------+---------+
| CustomerID | View | Click | Inquiry |
+------------+------+-------+---------+
|          1 |    1 |     1 |       1 |
|          2 |    1 |     1 |       0 |
|          3 |    0 |     2 |       0 |
+------------+------+-------+---------+

Is the view and click column is correct in the output??? – Praveen Sep 09 '16 at 04:57 — Praveen, Sep 09 '16 at 04:57
@Praveen Fixed, sorry for the confusion! – J Hsia Sep 09 '16 at 05:13 — J Hsia, Sep 09 '16 at 05:13

score 6 · Accepted Answer · answered Sep 09 '16 at 04:55

You can use case statement and sum like,

select 
    `CustomerID`,
    sum(case when `Activity` = 'View' then 1 else 0 end) `View`,
    sum(case when `Activity` = 'Click' then 1 else 0 end) `Click`,
    sum(case when `Activity` = 'Inquiry' then 1 else 0 end) `Inquiry`
from `tbl`
group by `CustomerID`
order by `CustomerID`

The output is

CustomerID  View    Click   Inquiry
1             1     1       1
2             1     1       0
3             0     2       0

Thank you so much! Great answer! – J Hsia Sep 09 '16 at 05:12 — J Hsia, Sep 09 '16 at 05:12

Count of data from one column into multiple columns with MySQL?

1 Answers1