Generated Series with Left Join

Software:

PostgreSQL 12 installed

Known 1:

I make query from 0 to 7 with this query

SELECT d FROM generate_series(0,7) d

Result

Known 2:

I have query to table_request like this:

select extract(hour from tr.lastupdate_time) as jam, tr.lastupdate_time, SUM(product_quantity) AS jumlah from table_request tr where date(tr.lastupdate_time) = current_date group by tr.lastupdate_time 

Result:

Wanted:

I want to join both query and the wanted result like this :

Solution:

SELECT d,tbtr.jam, tbtr.jumlah
FROM generate_series(0,7) d left join (select extract(hour from tr.lastupdate_time) as jam, tr.lastupdate_time,SUM(product_quantity) AS jumlah from table_request tr where date(tr.lastupdate_time) = current_date group by tr.lastupdate_time) tbtr
on extract(hour from tbtr.lastupdate_time)=d  order by d

Important Note:

Ensure that “where clause” inside each table not for all table because if “where clause” for all table then left join acting like inner join

Thanks for reading

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *