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







All Articles