Postgresql - Group By

user_5421292 0
Viewed 139

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

enter image description here

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

enter image description here

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.

grafana group-by postgresql sql
3 Answers

You may use a calendar table approach here:

SELECT
    t1.target_date,
    t2.type,
    COUNT(t3.target_date) AS count
FROM (SELECT DISTINCT target_date FROM yourTable) t1
CROSS JOIN (SELECT DISTINCT type FROM yourTable) t2
LEFT JOIN yourTable t3
    ON t3.target_date = t1.target_date AND
       t3.type = t2.type
GROUP BY
    t1.target_date,
    t2.type
ORDER BY
    t1.target_date,
    t2.type;

The idea here is to cross join subqueries finding all distinct target dates and types, to generate a starting point for the query. Then, we left join this intermediate table to your actual table, and find the counts for each date and type.

user_18632290

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

user_94737640 
select t.target_date, tmp.type, sum(case when t.type = tmp.type then 1 else 0 end) 
from your_table t
cross join (select distinct type from your_table) tmp
group by t.target_date, tmp.type

Demo

user_5753760
Related