Postgresql - Group By

Question

I have a simple groupby scenario. Below is the output of the query.

Query is: select target_date, type, count(*) from table_name group by target_date, type

The query and output is perfectly good. My problem is I am using this in Grafana for plotting. That is Grafana with postgres as backend. What happens is since "type2" category is missed on 01-10-2020 and 03-10-2020, type2 category never gets plotted (side to side bar plot) at all. Though "type2" is present in other days.

It is expecting some thing like

So whenever a category is missed in a date, we need a count with 0 value. Need to handle this in query, as the source data cannot be modified. Any help here is appreciated.

Answer 1

You need to create a list of all the target_date/type combinations. That can be done with a CROSS JOIN of two DISTINCT selects of target_date and type. This list can beLEFT JOINed to table_name to get counts for each combination:

SELECT dates.target_date, types.type, COUNT(t.target_date)
FROM (
  SELECT DISTINCT target_date
  FROM table_name
) dates
CROSS JOIN (
  SELECT DISTINCT type
  FROM table_name
) types
LEFT JOIN table_name t ON t.target_date = dates.target_date AND t.type = types.type
GROUP BY dates.target_date, types.type
ORDER BY dates.target_date, types.type

Demo on dbfiddle