How do I return a row with 0 if the group by has 0 records?

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

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.

Advertisement

Answer