Convert vchars to float and take average

Question

Hello Community,
I am a newbie when it comes to MSSQL so I apologize for my simple questions in advance. I have created a database which works great for our application. I am now a the point where I would like to take averages of a specified amount of rows (e.g. last 3 rows). The data which I’m averaging has a column title F1 and is of the varchars() data type. I’ve learned that before I run any mathematical operation on my data, I must convert it to float.

I can perform these tasks separately but not all in 1.

This works fine. It converts F1 to float and takes the average:

USE mydatabase
SELECT 
ROUND (AVG(CONVERT(float,ISNULL(F1,0))),1) AS F1average,
FROM  mytable
WHERE lineselected like 'batphone'

This works fine:

SELECT *
FROM mytable
WHERE lineselected like 'batphone'
ORDER BY ID DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY

But when I try to combine to only take the latest 3 rows , I get an error:

USE mydatabase
SELECT 
ROUND (AVG(CONVERT(float,ISNULL(F1,0))),1) AS F1average,
FROM  mytable
WHERE lineselected like 'batphone'
ORDER BY F1 DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY

Msg 8127, Level 16, State 1, Line 12
Column "mytable.F1" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Thank you in advance.

Answer 1

Please check if this is what you need. If not then please check my comment above and provide the missing information

 -- This returns the ROUND  of the AVG of only the three last rows, which has "batphone" as value of lineselected
;With MyCTE as (
 SELECT * FROM T
 WHERE lineselected like 'batphone'
 ORDER BY ID DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
)
SELECT ROUND (AVG(CONVERT(float,ISNULL(F1,0))),1)
FROM  MyCTE
GO