I have this alert_levels table:
| id | levels |
-----------------
| 1 | critical |
| 2 | error |
| 3 | warning |
| 4 | info |
Then I have this alerts table
| id | alert_time | alert_level_id | alert_type |
--------------------------------------------------------------
| 1 | 2020-03-01 08:01:00.000 | 4 | Type 1 |
| 2 | 2020-03-03 10:58:00.000 | 4 | Type 1 |
| 3 | 2020-03-17 09:05:00.000 | 4 | Type 2 |
| 4 | 2020-03-21 21:03:00.000 | 4 | Type 2 |
| 5 | 2020-03-27 23:10:00.000 | 4 | Type 1 |
| 6 | 2020-04-10 05:49:00.000 | 4 | Type 2 |
| 7 | 2020-04-10 06:29:00.000 | 4 | Type 2 |
| 8 | 2020-04-14 18:56:00.000 | 4 | Type 2 |
| 9 | 2020-04-19 22:34:00.000 | 4 | Type 2 |
The alert_level_id in the alerts table is a foreign key of id from the alert_levels table.
What I want is to count the number of occurences of each alert_type grouped by the alert_level_id whithin a chosen time period. And if there is no occurency then it should show 0.
This is how it should look like:
| alert_level_id | type_1_count | type_2_count | total_count|
-------------------------------------------------------------
| 1 | 0 | 0 | 0 |
| 2 | 0 | 0 | 0 |
| 3 | 0 | 0 | 0 |
| 4 | 9 | 130 | 139 |
I’ve tried something like this:
SELECT al.id,
count(CASE WHEN alert_type = 'Type 1' THEN 1 END) type_1_count,
count(CASE WHEN log_type = 'Type 2' THEN 1 END) type_2_count,
count(CASE WHEN log_type = 'Type 1' OR log_type = 'Type 2' THEN 1 END) total_count
FROM alert_levels al
LEFT JOIN alerts a ON al.id = a.alert_level_id
WHERE a.alert_time >= ? AND a.alert_time < ?
GROUP BY al.id
ORDER BY al.id ASC;
The first thing with this query is that I feel like there is a simpler query for this, and secondly if there is only alerts with a an alert_level_id 4 in the chosen period, it only retuns one the row with that alert level. But I always want all 4 rows returned..
Advertisement
Answer
In Postgres, you can use filter
for conditional aggregation:
SELECT al.id,
count(*) FILTER (WHERE a.alert_type = 'Type 1') as type_1_count,
count(*) FILTER (WHERE a.alter_type = 'Type 2') as type_2_count,
COUNT(a.id) as total_count
FROM alert_levels al LEFT JOIN
alerts a
ON al.id = a.alert_level_id AND
a.alert_time >= ? AND a.alert_time < ? AND
a.type in ('Type 1', 'Type 2')
GROUP BY al.id
ORDER BY al.id ASC;
Also note the conditions that have been moved to the ON
clause.