I have this query and the the result is in multiple rows, each one is a event and his respective count number.
SELECT u.name, l.event, COUNT (l.event)
FROM log AS l LEFT JOIN user AS u ON u.id = l.userid
GROUP BY u.name, l.event
The result is like this:
------------------------------------------
| user | event | count |
------------------------------------------
| user_1 | event_1 | 12 |
| user_1 | event_2 | 6 |
| user_1 | event_3 | 9 |
| user_2 | event_1 | 16 |
| ... | ... | ... |
The problem is that i need these count as parameters (columns) in a single result row, where each row represents a single user, something like this:
--------------------------------------------------------------
| user | event_1 | event_2 | event_3 |
--------------------------------------------------------------
| user_1 | 12 | 6 | 9 |
| user_2 | 16 | 0 | 13 |
| ... | ... | ... | ... |
Maybe i can do this with a select query? With some kind of function that does a loop or something similar?
Thank you!
EDIT 1: I do not know the events to write them in the code
EDIT 2: I see that there is no trivial way to do this with a simple sql query, so i use python with pandas to do this. Take a look in How to convert a column of string to numerical?