Q:

How to return highest average hourly value by year

I have an SQL table of readings taken every 10 minutes.
Columns are Date_Time (DateTime) and Steam_Flow (Real).
I would like to get a summary that only returns one row for each year that represents the highest average flow (by hour) for that year.

I have the following so far...
SELECT
    CAST(Date_Time AS DATE) [Date],
    DATEPART(hour, Date_Time) [Hour],
    Avg(Steam_Flow) [Avg Flow]
FROM Boiler_Data
WHERE
    Date_Time >= '2018-12-31 21:00'
    and Date_time <= '2019-01-01 02:50:'
GROUP BY
    CAST(Date_Time AS DATE),
    DATEPART(hour, Date_Time)
ORDER BY [Avg Flow] Desc

A:

You could use an aggregated query to compute the average flow per hour, and then aggregate by year in the outer query.
In MySQL :
SELECT x.date_year, MAX(x.Avg_Flow)
FROM (
    SELECT YEAR(date_time) date_year, avg(Flow) Avg_Flow
    FROM mytable 
    GROUP BY YEAR(date_time), DATE_FORMAT(date_time, '%Y-%m-%d %h')
) x
GROUP BY x.date_year

In SQL Server :
SELECT x.date_year, MAX(x.Avg_Flow)
FROM (
    SELECT YEAR(date_time) date_year, avg(Flow) Avg_Flow
    FROM mytable 
    GROUP BY YEAR(date_time), DATEADD(hour, datediff(hour, 0, date_time), 0)
) x
GROUP BY x.date_year

