I have a MySQL Table as for example
Date | Branch | shift_time | Total Order | Avg Price
20-06-08 | A | morning | 4 | 5.6
20-06-08 | A | night | 3 | 3.4
20-06-08 | B | morning | 2 | 2.7
20-06-08 | B | night | 6 | 5.9
20-06-09 | A | morning | 9 | 8.9
20-06-09 | A | night | 4 | 6.9
The column shift_time is an enum and will be constant We need to convert that into single record per each date by branch
Date | Branch | morning_total_order | morning_price | night_total_order | night_avg_price
20-06-08 | A | 4 | 5.6 | 3 | 3.4
20-06-08 | B | 2 | 2.7 | 6 | 5.9
20-06-09 | A | 9 | 8.9 | 4 | 6.9
I tried using GROUP_CONCAT but that query merges the shift time with data. We want that on the column header. Maybe we need to use CASE WHEN. But I am not sure about that.
You can use conditional aggregation to generate the results you want:
SELECT Date, Branch,
SUM(CASE WHEN shift_time = 'morning' THEN `Total Order` ELSE 0 END) AS morning_total_order,
SUM(CASE WHEN shift_time = 'morning' THEN `Avg Price` * `Total Order` ELSE 0 END) /
SUM(CASE WHEN shift_time = 'morning' THEN `Total Order` ELSE 0 END) AS morning_avg_price,
SUM(CASE WHEN shift_time = 'night' THEN `Total Order` ELSE 0 END) AS night_total_order,
SUM(CASE WHEN shift_time = 'night' THEN `Avg Price` * `Total Order` ELSE 0 END) /
SUM(CASE WHEN shift_time = 'night' THEN `Total Order` ELSE 0 END) AS night_avg_price
FROM shifts
GROUP BY Date, Branch
Output:
Date Branch morning_total_order morning_avg_price night_total_order night_avg_price
20-06-08 A 4 5.6 3 3.4
20-06-08 B 2 2.7 6 5.9
20-06-09 A 9 8.9 4 6.9