Szworny Dziąch Szworny Dziąch - 3 months ago 10
SQL Question

Getting sells statistics for given period in PostgreSQL

I'm trying to get some simple statistics about sells in a period chosen by user. I have this query:

SELECT
"orders_order"."created_at"::date,
SUM(("meals_meal"."discount_price" * "orders_orderitem"."amount")) AS "price"
FROM "orders_order"
LEFT OUTER JOIN "orders_orderitem" ON ("orders_order"."id" = "orders_orderitem"."order_id")
LEFT OUTER JOIN "meals_meal" ON ("orders_orderitem"."meal_id" = "meals_meal"."id")
WHERE ("orders_order"."restaurant_id" = 36
AND "orders_order"."created_at" BETWEEN '2016-08-01T00:00:00+02:00'::timestamptz AND '2016-08-31T00:00:00+02:00'::timestamptz)
GROUP BY "orders_order"."created_at"
ORDER BY "orders_order"."created_at" ASC


Which gives me result set looking like that:

enter image description here

Now what I'd like to achieve is to have my results grouped by the
created_at
column with prices summed up. So the correct output for such query should be like:

created_at | price
-------------------
2016-08-14 | 185.00

Answer

Should be

SELECT 
    "orders_order"."created_at"::date,
    SUM(("meals_meal"."discount_price" * "orders_orderitem"."amount")) AS "price" 
FROM "orders_order" 
LEFT OUTER JOIN "orders_orderitem" ON ("orders_order"."id" = "orders_orderitem"."order_id") 
LEFT OUTER JOIN "meals_meal" ON ("orders_orderitem"."meal_id" = "meals_meal"."id") 
WHERE ("orders_order"."restaurant_id" = 36 
    AND "orders_order"."created_at" BETWEEN '2016-08-01T00:00:00+02:00'::timestamptz AND '2016-08-31T00:00:00+02:00'::timestamptz) 
GROUP BY "orders_order"."created_at"::date
ORDER BY "orders_order"."created_at"::date ASC
Comments