SQLTeam.com | Weblogs | Forums

Average of minimum and maximum

I have the following code to calculate the min,max and average of temperature per month. However I also want to determine the average minimum and average maximum per month.

declare @cnt int = 1

while @cnt < 13
begin

select maand, min(Stasie1) as minStasie1, max(stasie1) as maksStasie1, avg(stasie1) as gemStasie1
from GekombineerAlleMaande
where maand = @cnt
group by maand

set @cnt = @cnt +1
end;

Any help would be much appreciated.

Regards

Provide sample data.

select maand, 
min(Stasie1) as minStasie1, 
max(stasie1) as maksStasie1, 
avg(stasie1) as gemStasie1
from GekombineerAlleMaande
where maand between 1 and 13
group by maand

Hi Yosiaz, thank you

This just gives the minimum or maximum for the month, not the average of the minimums/maximums.

I would love to provide sample data via csv, but cannot figure out how to do that (can only see upload an image file).

Regards

I think that this is what you are after, you need a set of intermediate results that you can then query (for the monthly output) and average out (the overall average of minimums)... the below does it all in one hit

; with temperature as ( -- a dummy table of temperatures
	select * from (
		values
			(1, 10), (1, 15), (1, 8)
			, (2, 15), (2, 25), (2, 30)
			, (3, 9), (3, 16), (3, 56), (3, 9)

	) d(monthnumber, temp)
)
-- what is the average min / max per month 
select monthnumber, min(temp) as [min], max(temp) as [max], avg(temp) as [avg]
from temperature
group by monthnumber

union all

-- what is the average of the min / max above
select 13 as dummymonth, avg([min]), avg([max]), avg(d.avg) from (
	-- repeat the monthly min/max'ing and then average the result in the outer query
	select monthnumber, min(temp) as [min], max(temp) as [max], avg(temp) as [avg]
	from temperature
	group by monthnumber
) as d
order by monthnumber asc
1 Like

hi

you will have to use cte's

hope this helps :slight_smile: :slight_smile:

;with cte
(
min max query
), cteAvgMinMax
(
avg min
avg max
from cte
)
final select here
go