Skip to content
Advertisement

SQL request in which the data of one field as a result is grouped in different columns

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
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement