0

The title seems a bit confusing, but I am struggling to put down what I need this query to do so best to explain it. I have 3 tables in my database (Using MySQL Workbench), but for this query I'm just trying to use one.

The table named service_data has the following columns:

Services_ID Service_Type Day Time Customer_ID(FK)
1001 SERVICE1 Mon 0950 1
1002 SERVICE2 Tue 1032 65
1003 SERVICE3 Wed 0859 4

The table contains approx. 200 records, my aim is to group the timings together, which I have managed to achieve by doing this:

select 
  case WHEN (
    Delivery_Time between '08:00:00' 
    and '09:00:00'
  ) then '0800-0900' WHEN (
    Delivery_Time between '09:00:00' 
    and '10:00:00'
  ) then '0900-1000' WHEN (
    Delivery_Time between '10:00:00' 
    and '11:00:00'
  ) then '1000-1100' WHEN (
    Delivery_Time between '11:00:00' 
    and '12:00:00'
  ) then '1100-1200' WHEN (
    Delivery_Time between '12:00:00' 
    and '13:00:00'
  ) then '1200-1300' WHEN (
    Delivery_Time between '13:00:00' 
    and '14:00:00'
  ) then '1300-1400' WHEN (
    Delivery_Time between '14:00:00' 
    and '15:00:00'
  ) then '1400-1500' WHEN (
    Delivery_Time between '15:00:00' 
    and '16:00:00'
  ) then '1500-1600' WHEN (
    Delivery_Time between '16:00:00' 
    and '17:00:00'
  ) then '1600-1700' WHEN (
    Delivery_Time between '17:00:00' 
    and '18:00:00'
  ) then '1700-1800' WHEN (
    Delivery_Time between '18:00:00' 
    and '19:00:00'
  ) then '1800-1900' WHEN (
    Delivery_Time between '19:00:00' 
    and '20:00:00'
  ) then '1900-2000' WHEN (
    Delivery_Time between '20:00:00' 
    and '21:00:00'
  ) then '2000-2100' else 'Outside Opening Hours' end as `Time Period`, 
  count(0) as 'count' 
from 
  service_data 
group by 
  `Time Period` 
order by 
  count desc 
limit 
  20;

Which produces the below result:

TimePeriod Count
1700-1800 24
1500-1600 21
1200-1300 19
1400-1500 19
1800-1900 17
1100-1200 17
1300-1400 16
1600-1700 16
1000-1100 16
1900-2000 12
0800-0900 12
0900-1000 11

What I am now trying to do is split the count up so that there are 4 columns labelled SERVICE1, SERVICE2, SERVICE3 and SERVICE4 (the values within the Service_Type column), so that it looks something like this:

TimePeriod SERVICE1 SERVICE2 SERVICE3 SERVICE4
1700-1800 6 7 10 1
1500-1600 5 9 1 6
1200-1300 0 4 2 13

Is this possible? Any help would be appreciated.

1
  • It is referred as 'pivot', but MySQL knows nothing about it.
    – Akina
    Mar 15, 2019 at 15:30

1 Answer 1

0

'Bug': Since BETWEEN is inclusive, a 10:00 delivery will be in the '0900-1000' bucket.

See the [pivot] tag for discussion of how to solve your problem. Here's a crude attempt:

SELECT  CONCAT(hr, "-", hr+1, "00") AS TimePeriod
        SUM(IF(service_type = 'SERVICE1', ct, 0)) AS SERVICE1,
        SUM(IF(service_type = 'SERVICE2', ct, 0)) AS SERVICE2,
        SUM(IF(service_type = 'SERVICE3', ct, 0)) AS SERVICE3,
        SUM(IF(service_type = 'SERVICE4', ct, 0)) AS SERVICE4
    FROM (
        SELECT  HOUR(Delivery_Time) AS hr,
                service_type,
                COUNT(*) as ct
            FROM service_data
            GROUP BY 1,2
         ) AS x

Notice that I approached the hours in a different way.

To do "outside", change CONCAT(hr, "-", hr+1, "00") AS TimePeriod to

IF ( hr BETWEEN 8 AND 20,   -- assuming hours are 08:00 to 20:59
     CONCAT(hr, "-", hr+1, "00"),
     'Outside Opening Hours' )  AS TimePeriod

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.