The query below provides the result in the image, however I want to the sum the NewOrders and DoneOrders for the same dates.Already i'm getting data for the past 7 days.
SELECT DATE(pickup_date),
COUNT(CASE WHEN order_status ='New' THEN 1 END) AS "NewOrders",
COUNT(CASE WHEN order_status ='Done' THEN 1 END) AS "DoneOrders"
from requests where DATE(pickup_date) > current_date::date - INTERVAL '7 days' GROUP BY pickup_date
ORDER BY pickup_date;
You need to fix the GROUP BY. Your aggregation key is the date with the time. You want to convert the value to a date:
SELECT pickup_date::date,
COUNT(CASE WHEN order_status ='New' THEN 1 END) AS "NewOrders",
COUNT(CASE WHEN order_status ='Done' THEN 1 END) AS "DoneOrders"
FROM requests
WHERE pickup_date::date > current_date- INTERVAL '7 days'
GROUP BY pickup_date::date
ORDER BY pickup_date::date;
Postgres also supports FILTER, which is a cleaner way to write the logic. In addition, current_date is a date, so it does not need to be converted, and double quotes are not needed, so don't use them!
SELECT pickup_date::date,
COUNT(*) FILTER (WHERE order_status = 'New') AS NewOrders,
COUNT(*) FILTER (WHERE order_status = 'Done') AS DoneOrders
FROM requests
WHERE pickup_date::date > current_date - INTERVAL '7 days'
GROUP BY pickup_date::date
ORDER BY pickup_date::date;
sum(price) filter (where order_status='Done') as PriceSum in above query.