TSQL Average

Question

Hi,

I have a table like the below.

  create table Averages (id int, Year_Mnth int, Counts int)
  insert into Averages values (372, 202303, 13)
							  ,(372, 202303, 7)
							  ,(372, 202304, 3)
							  ,(372, 202305, 16)
							  ,(372, 202306, 9)
							  ,(372, 202307, 17)

I want to select the average of the first 5 months (thus 202302 through 202306) as a new column into a temp table, as shown below.

Please assist,

Thanks

Answer 1

I hope that I understood your goal. Have a look at the following code and feel free to change it according to your needs:

create table #Averages (id int, Year_Mnth int, Counts int)
  insert into #Averages values (372, 202302, 13)
							  ,(372, 202303, 7)
							  ,(372, 202304, 3)
							  ,(372, 202305, 16)
							  ,(372, 202306, 9)
							  ,(372, 202307, 17);


WITH CTE as(
	Select *, SUM(Cast(Counts as decimal(10,2))) OVER (ORDER BY Year_Mnth) as MovingSum
			, Count(Cast(Counts as decimal(10,2))) OVER (ORDER BY Year_Mnth) as MovingCount
			, AVG(Cast(Counts as decimal(10,2))) OVER (ORDER BY Year_Mnth) as MovingAvg
			, ROW_NUMBER() OVER (ORDER BY Year_Mnth) as RowNumber
	from #Averages
)
Select *
from CTE
--where Year_Mnth between 202302 and 202306
where RowNumber = 5
order by Year_Mnth;

Answer 2

To calculate the average for the first 5 months in that table, you'll have to create a temporary table with the following:

Create temporary table TempAverages as

SELECT

  id,

  AVG(Counts) AS AvgCounts

FROM

  Averages

WHERE

  Year_Mnth >= 202302 AND Year_Mnth <= 202306

GROUP BY

  id;

#selects the contents of the temporary table.

 

SELECT * FROM TempAverages;

Kindly drop the feed back