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
beginselect maand, min(Stasie1) as minStasie1, max(stasie1) as maksStasie1, avg(stasie1) as gemStasie1
from GekombineerAlleMaande
where maand = @cnt
group by maandset @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 aschi
you will have to use cte's
hope this helps 
;with cte
(
min max query
), cteAvgMinMax
(
avg min
avg max
from cte
)
final select here
go