Stuck with database query-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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