MySQL – Split results of count function into columns derived from a separate column

countMySQLmysql-workbenchpivot

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.

Best Answer

'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
Related Question