MYSQL: How to aggregate events data

Question

I have a table stored events data in the following form

User   Event_type
1      A
3      B
1      B
2      C

I want to count for each user number of certain type of events (Not all user have all events)

expected result

User   #Event_A  #Event_B   #Event_C
1      1         1          0
2      0         0          1
3      0         1          0

How can I achieve this most concisely ?

This transformation is called pivoting and has been asked and answered here on SO several times. The linked duplicate topic escribes both static and dynamic pivoting. Pls note that it may be more effective to perform such transformations within the application logic, rather than in sql. — Shadow, Jul 11 '17 at 15:28

score 0 · Accepted Answer · answered Jul 11 '17 at 15:26

0

SELECT
    `User`,
    SUM(IF(Event_type = "A", 1, 0)) AS "#EventA",
    SUM(IF(Event_type = "B", 1, 0)) AS "#EventB",
    SUM(IF(Event_type = "C", 1, 0)) AS "#EventC"
FROM
    yourtable
GROUP BY
    `User`

If you were going to have a ton of events, you could build the SUM IF statements dynamically in a loop using the results of SELECT DISTINCT(Event_type) FROM yourtable

answered Jul 11 '17 at 15:26

user2278120

623
2
9
22

No need to loop, see the duplicate topic. – Shadow Jul 11 '17 at 15:30
I see thanks. It may be easier though to loop in a scripting language and just build strings like 'SUM(IF(Event_type = "'+event+'", 1, 0)) AS "#Event'+event+'"', and just get the job done though in some cases. – user2278120 Jul 11 '17 at 17:06
The whole pivoting transformation is usually more effectively done in a proper programming language, than in SQL :) – Shadow Jul 11 '17 at 17:13

MYSQL: How to aggregate events data

1 Answers1