MySQL sort COUNT into different columns

Question

I have the following table structure

ID | capacity | startDateTime      
1  | 2        | 2013-11-26 13:23:23
2  | 3        | 2013-11-26 14:23:21
3  | 2        | 2013-11-26 13:44:44
4  | 6        | 2013-11-26 14:24:22

I am trying to get the count-of-IDs for categories-of-capacity grouped into hour and 30 minute intervals.

An example of my output is

Hour | Minute | Capacity1-2 | Capacity3-4 | Capacity5above
13   | 0      | 1           | 0           | 0
13   | 1      | 1           | 0           | 0
14   | 0      | 0           | 1           | 1

Minute=0 is for IDs with startDateTime before the 30th minute of the hour (e.g. 13:00:00 to 13:29:29) and Minute=1 is for IDs with `startDateTime after the 30th minute of the hour.

I have came as far as to writing something like this but it gives me the total transactions. I am having problems putting the category-of-capacity into separate columns.

SELECT HOUR(startDateTime) AS Hour, 
  FLOOR(MINUTE(startDateTime)/30) AS Minute,
  COUNT(DISTINCT ID) as numTransactions 
  FROM transaction t 
  WHERE startDateTime >= '2013-11-26 00:00:00'
   AND queueStartTime <= '2013-11-26 23:59:59'  
  GROUP BY HOUR(startDateTime), FLOOR(MINUTE(startDateTime)/30)

Filipe Silva · Accepted Answer · 2013-11-26 13:36:34Z

1

Try this:

SELECT HOUR(startDateTime) AS Hour,
  FLOOR(MINUTE(startDateTime) / 30) AS Minute,
  SUM(CASE WHEN capacity = 1 OR capacity = 2 THEN 1 ELSE 0 END) AS Capacity1_2,
  SUM(CASE WHEN capacity = 3 OR capacity = 4 THEN 1 ELSE 0 END) AS Capacity3_4,
  SUM(CASE WHEN capacity > 4 THEN 1 ELSE 0 END) AS Capacity5Above
FROM TRANSACTION t
WHERE startDateTime >= '2013-11-26 00:00:00'
  AND queueStartTime <= '2013-11-26 23:59:59'
GROUP BY HOUR(startDateTime),
  FLOOR(MINUTE(startDateTime) / 30)

Instead of count this will instead SUM 1 for each of the records that match the CASE WHEN validation. Which would be the same as having a count for each of the capacity ranges.

answered Nov 26, 2013 at 13:36

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL sort COUNT into different columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related