How can I query the results of a MySQL query and get a COUNT() of the results that fall within a certain range?

thanks for taking the time.

I have the following query:

SELECT bike_id, 
  COUNT(trip_id) AS "trip_count", 
  COUNT(trip_id)/365 AS "avg_rides_per_day"
FROM 2017
GROUP BY bike_id;

Which gives me the results I want which are the distinct ‘bike_id'(as there are no duplicates), the count of all the rides each ‘bike_id’ made in 2017, and the quick average for the year for each ‘bike_id’.

From here, id like to know if I can have it display the COUNT() of the “avg_rides_per_day” in different ranges, from this same result of the query above(since this info doesn’t exist in the table without using this query). For example:

0.0000-0.5000 has 328
0.5001-1.0000 has 211
1.0001-1.5000 has 101 (but in a table format, the ranges being the headers)

Hope this makes some sense. I feel its a quick problem to solve but I’m not good(total newbie).

Thank you!

Answer

So i figured it out so hopefully if any new people run across this issue this can help you a little bit!:

SELECT COUNT(CASE WHEN avy BETWEEN 0.0000 AND 0.5000 THEN 1 ELSE NULL END) AS "0.0000-0.5000",
              COUNT(CASE WHEN avy BETWEEN 0.5001 AND 1.0000 THEN 1 ELSE NULL END) AS "0.5001-1.0000",
              COUNT(CASE WHEN avy BETWEEN 1.0001 AND 1.5000 THEN 1 ELSE NULL  END) AS "1.0001-1.5000",
              COUNT(CASE WHEN avy BETWEEN 1.5001 AND 2.0000 THEN 1 ELSE NULL END ) AS "1.5001-2.0000",
                    COUNT(CASE WHEN avy BETWEEN 2.0001 AND 2.5000 THEN 1 ELSE NULL  END) AS "2.0001-2.5000",
                    COUNT(CASE WHEN avy BETWEEN 2.5001 AND 3.0000 THEN 1 ELSE NULL END ) AS "2.5001-3.0000",
                    COUNT(CASE WHEN avy BETWEEN 3.0001 AND 3.5000 THEN 1 ELSE NULL END ) AS "3.0001-3.5000"
FROM (SELECT `bike_id`,
                1 * COUNT(`trip_id`)/365 AS avy
              FROM `2021`
              GROUP BY `bike_id`) AS averages;

The result is everything neatly in its own column, indicating range(as the column header) and the count of unique bikes that fall in that range(as the only entry in each column).