Count Multiple Events in One Query
Use this query to count different events in one query using CASE instead of JOIN.Each column will show a count of a different event.
Syntax:
SUM(CASE WHEN event_name = 'event1name' THEN 1 ELSE 0 END) AS event1count,
Example:
SELECT
DATE(event_time_ts) AS date,
SUM(CASE WHEN event_name = 'Signup' THEN 1 ELSE 0 END) AS Signup,
SUM(CASE WHEN event_name = 'Login' THEN 1 ELSE 0 END) AS Login
FROM cooladata
WHERE date_range(context) AND slicers(context)
GROUP BY date
Output:
date | Signup | Login |
2013-10-01 | 8 | 20 |
2013-10-02 | 13 | 19 |
2013-10-03 | 13 | 24 |
2013-10-04 | 18 | 28 |
2013-10-05 | 10 | 14 |
2013-10-06 | 15 | 29 |
2013-10-07 | 8 | 15 |
2013-10-08 | 11 | 23 |
2013-10-09 | 17 | 25 |
2013-10-10 | 13 | 20 |