Question 90791 in PostgreSQL Question posted in
The official documentation can be found here.

Stuck with database query – Postgresql

If I run this query:

select date, sum(orders) as "Total orders" 
from tbl 
where date >=1 and date <= 4 
group by date

How can I get the expected output as shown in the screenshot?

enter image description here

Tags:

2

Answers


  1. 0 
    WITH cte as (

SELECT date, orders
FROM dbo.Orders

),

cte2 as (

select top (SELECT MAX(date) FROM cte) ROW_NUMBER() over(order by a.name) 
as date
from sys.all_objects a

) 

SELECT cte2.date, SUM(ISNULL(orders, 0))
FROM cte2 LEFT JOIN cte ON cte2.date = cte.date
GROUP BY cte2.date
    Login or Signup to reply.
  2. 0

    Use a left against generate_series():

    select g.date, 
       sum(tbl.orders) as "Total orders" 
from generate_series(1,4) as g(date)
  left join tbl on tbl.date = g.date 
group by g.date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search