An example of a table of orders
Table: orders +-------------+----------+-------------------------------+ | date | price | type (delivery or pickup) | +-------------+----------+-------------------------------+ | 2022-01-12 | 113 | delivery | | 2022-01-12 | 23 | pickup | | 2022-04-12 | 45 | pickup | | 2022-05-17 | 17 | delivery | | 2022-05-17 | 58 | delivery | | 2021-08-13 | 13 | pickup | | 2021-08-24 | 24 | pickup | +---------+----------+-----------------------------------+
Is it possible to get the amount of orders grouped by dates without the use of subqueries, while separately the pickup amount and delivery amount?
An example of the result: +-------------+--------------------+-------------------+ | date | amount of delivery | amount of pickup | +-------------+--------------------+-------------------+ | 2022-01-12 | 113 | 68 | | 2022-01-17 | 75 | 0 | | 2022-01-13 | 0 | 13 | | 2022-01-24 | 0 | 24 | +-------------+--------------------+-------------------+
I’m not sure that it is possible
Advertisement
Answer
you can wrap you condition into aggregation
select t.date ,sum(case when t.type = 'delivery' then t.price else 0 end) as amount_of_delivery ,sum(case when t.type = 'pickup' then t.price else 0 end) as amount_of_pickup from table_of_orders t group by t.date
for mysql syntax it can be as follows:
select t.date ,sum(if(t.type = 'delivery', t.price, 0)) as delivery ,sum(if(t.type = 'pickup', t.price, 0)) as pickup from table_of_orders t group by t.date