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.

  • It is referred as 'pivot', but MySQL knows nothing about it. – Akina Mar 15 at 15:30
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, privacy policy and cookie policy

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