Skip to content
Advertisement

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

I have this alert_levels table:

Then I have this alerts table

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:

I’ve tried something like this:

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:

Also note the conditions that have been moved to the ON clause.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement