I want to produce a list of sales (in postgres) grouped by day of the week and For each day of the week, get the number of sales and total profit. My coalesce doesnot seem to be working.

SELECT 
    to_char(order_time,'Dy'::TEXT) AS day,
    coalesce(COUNT(menu_item_id),0) AS qty_sold,
    coalesce(SUM(price - cost) ,0)AS total_profit
from sales_order AS s 
    inner join order_item as o on o.sales_order_id=s.id
    inner join menu_item AS m on  m.id=o.menu_item_id
where s.order_time > date_trunc('week', now())::date - 5
group by day
order by day asc

CodePudding user response：

It sounds like you are missing dates from your report when there were no sales on those dates.

Use generate_series() to create the dates of the report, and then left join into your orders:

with report_dates as (
  select gs.ddate::date, min(gs.ddate::date) over () as begin_date
    from generate_series(
           date_trunc('week', now()) - interval '5 days',
           date_trunc('week', now()),
           interval '1 day'
         ) as gs(ddate)
)
select r.ddate, 
       to_char(r.ddate,'Dy'::TEXT) as day,
       coalesce(count(menu_item_id), 0) as qty_sold,
       coalesce(sum(price - cost), 0) as total_profit
  from report_dates r
       left join sales_order s 
         on s.order_time >= r.begin_date
        and s.order_time::ddate = r.ddate
       left join order_item o on o.sales_order_id=s.id
       left join menu_item m on m.id=o.menu_item_id
 group by r.ddate, day
 order by day