0

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.

Saqib
  • 82
  • 7

1 Answers1

1

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

Demo on SQLFiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • Can you please let me know about that line. `SUM(CASE WHEN shift_time = 'morning' THEN 'Avg Price' * `Total Order` ELSE 0 END) / ` Really appreciate that I have tried and the answer seems to be working. Thanks a lot. – Saqib Jun 11 '20 at 06:54
  • @Saqib what that does is it sums the products of `Avg Price` and `Total Order` for a given branch and date (since they are what we are grouping by), but *only* if the `shift_time` column is equal to `morning`, so this gives us the morning shift values. We can then do the same for `shift_time = 'night'` to get the night shift values. – Nick Jun 11 '20 at 06:57
  • Hi @Nick, I tried to run it on my MySQL. It generated results but figures like 92.48 is converted into 92.4800033569336 and one timestamp field is converted into a number like 20200605093000 – Saqib Jun 11 '20 at 07:06
  • @Saqib you can fix the `92.48000...` problem with `ROUND(value, 2)`, I'm not sure about the timestamp, you haven't included any in your question... – Nick Jun 11 '20 at 07:08
  • Hi @Nick, Thanks for your guidance, it really helped me a lot. Can you please have a look at this http://www.sqlfiddle.com/#!9/40f423/1 How can I get datetime instead of the long number? (I tried converting that into epoch unix time, didn't worked) Thanks – Saqib Jun 11 '20 at 23:21
  • @Saqib if there is only one value for `tick_time` for each `market` value, you can simply use `MAX` (or `MIN`) instead of `SUM`. See http://www.sqlfiddle.com/#!9/40f423/6 If there can be more than one, it becomes a more complicated problem, and I would recommend asking a new question – Nick Jun 11 '20 at 23:27
  • Thanks so much. really helped a lot. The problem is solved now, I really appreciate your time. Thanks again. – Saqib Jun 12 '20 at 00:02
  • @Saqib no worries. Have a good day, – Nick Jun 12 '20 at 00:22