J Hsia J Hsia - 2 months ago 7
MySQL Question

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

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 |
+------------+------+-------+---------+

Answer

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
Comments