SQL: getting data by date and range of a column
I have a MySQL table called "Library" below with three columns. I need to get the number of stay_time intervals for each date. The ranges I need are 0-30, 31-60, 61-90 and 91-100.
Id Date dwell_time 1 2015-05-10 27 2 2015-05-10 28 3 2015-05-10 32 4 2015-05-10 65 5 2015-05-11 27 6 2015-05-11 28 7 2015-05-11 65
should return
2015-05-10 0-30 2 2015-05-10 31-60 1 2015-05-10 61-90 1 2015-05-11 0-30 2 2015-05-11 61-90 1
Can you help with the query I need to use?
+3
source to share
1 answer
SELECT
date,
date_range,
count(1)
FROM (
SELECT
date,
CASE
WHEN dwell_time BETWEEN 0 AND 30 THEN '0-30'
WHEN dwell_time BETWEEN 31 AND 60 THEN '31-60'
WHEN dwell_time BETWEEN 61 AND 90 THEN '61-90'
ELSE '90-100'
END AS date_range
FROM Library) lib
GROUP BY date, date_range
EDIT (by Gordon, because I was asked in the comments):
SELECT date,
(CASE WHEN dwell_time BETWEEN 0 AND 30 THEN '0-30'
WHEN dwell_time BETWEEN 31 AND 60 THEN '31-60'
WHEN dwell_time BETWEEN 61 AND 90 THEN '61-90'
ELSE '90-100'
END) AS date_range,
COUNT(*)
FROM Library l
GROUP BY date, date_range
ORDER BY date, date_range;
Here is the SQL script.
+2
source to share