I have a list of orders and would like to output a list by day of when they were created at of (1) total orders and (2)orders that have a fulfillment_status of 'paid'

 id | fulfillment_status | created_at (timestamp witout time zone)
1                           2015-10-17 02:23:14.882131                                            
2      paid                 2015-10-18 02:23:14.882131
3                           2015-10-17 02:23:14.882131

so that the output is:

date         total_orders total_paid_orders
2015-10-17     1             0
2015-10-18     2             1     

I get this far:

 select count(*) from orders group by created_at ???

but ....

select created_at::date, 
       count(*) as total_orders,
       count(*) filter (where fulfillment_status = 'paid') as total_paid_orders
from orders
group by created_at::date
order by created_at::date;

created_at::date converts the timestamp into a date (without a time) and thus groups by day.

count(*) filter (where fulfillment_status = 'paid') only counts rows where the condition is true.

  • perfect, thx and thx for explanation - very helpful. need to wait a few minutes to accept. – timpone Nov 23 '15 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.