How to apply aggregate functions to results of other aggregate functions in single query?

Question

I have a table BIKE_TABLE containing columns Rented_Bike_Count, Hour, and Season. My goal is to determine average hourly rental count per season, as well as the MIN, MAX, and STDDEV of the average hourly rental count per season. I need to do this in a single query.

I used:

SELECT 
    SEASONS,
    HOUR,
    ROUND(AVG(RENTED_BIKE_COUNT),2) AS AVG_RENTALS_PER_HR
FROM TABLE
GROUP BY HOUR, SEASONS
ORDER BY SEASONS

and this gets me close, returning 96 rows (4 seasons x 24 hours per) like:

SEASON	HOUR	AVG_RENTALS_PER_HR
Autumn	0	709.44
Autumn	1	552.5
Autumn	2	377.48
Autumn	3	256.55

But I cannot figure out how to return the following results that use ROUND(AVG(RENTED_BIKE_COUNT) as their basis:

1. What is the average hourly rental count per season? The answer should be four lines, like: Autumn, [avg. number of bikes rented per hour]
2. What is the MIN of the average hourly rental count per season?
3. Same for MAX
4. Same for STDDEV.

I tried running

MIN(AVG(RENTED_BIKE_COUNT)) AS MIN_AVG_HRLY_RENTALS_BY_SEASON,
MAX(AVG(RENTED_BIKE_COUNT)) AS MAX_AVG_HRLY_RENTALS_BY_SEASON,
STDDEV(AVG(RENTED_BIKE_COUNT)) AS STNDRD_DEV_AVG_HRLY_RENTALS_BY_SEASON

as nested SELECT and then as nested FROM clauses, but I cannot seem to get it right. Am I close? Any assistance greatly appreciated.

Answer 1

I think that you are over complicating the task. Does this give you your answers? If not please tell me the difference between it's output and your desired output.
Of course you can add ROUND() to reach column etc as you see fit.

SELECT 
    SEASONS,
    MIN(RENTED_BIKE_COUNT) minimum,
    MAX(RENTED_BIKE_COUNT) maximum,
    STDDEV(RENTED_BIKE_COUNT) sDev,
    AVG(RENTED_BIKE_COUNT) average
FROM TABLE
GROUP BY SEASONS
ORDER BY SEASONS;

According to your comment It seems that you may want the following query.

WITH seasons AS(
  SELECT 
    Season,
    AVG(RENTED_BIKE_COUNT) seasonAverage
  FROM TABLE
  GROUP BY season)
SELECT
  AVG(seasonAverage) average,
  MIN(seasonAverage) minimum,
  MAX(seasonAverage) maximum,
  STDDEV(seasonAverage) sDev
FROM
  seasons;