Filter clause for aggregate functions

Question

It seems there is an error in my sql syntax. Here is the command I'm running.:

select day(deliverydate),
       sum(total_amount) FILTER (WHERE payment_option='EBS'),
       sum(total_amount) FILTER (WHERE payment_option='COD')
from orders
where month(deliverydate)=2 and
      year(deliverydate)=2019
group by day(deliverydate)
order by 1;

Is there a better way for the output to be: the nos will be from data obviously

Any help is greatly appreciated.

Answer 1

I would recommend writing this as:

select day(deliverydate),
       sum(case when payment_option = 'EBS' then total_amount end),
       sum(case when payment_option = 'COD' then total_amount end)
from orders
where deliverydate >= '2019-02-01' and
      deliverydate < '2019-03-01'
group by day(deliverydate)
order by 1;

Note the change to the date logic. This makes the where clause more index-friendly.

Also, if deliverydate has no time component, there is no need for day(deliverydate). You can just use deliverydate in the select and group by clauses. This makes it easier to extend the query to multiple months. If there is a time component, use date() instead of day().

If you have lots of other payment options, then adding and payment_option in ('EBS', 'COD') to the where clause might also help performance.