question

ceramicprofiler avatar image
0 Votes"
ceramicprofiler asked pituach commented

Convert vchars to float and take average

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.




sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day and welcoem to the QnA forums

I apologize for my simple questions in advance.

You have no reason to apologize and there is not even one expert which was not a newbie first :-)

Your request is not fully clear to me. Your first query take average of all rows in the table and you second query returns only 3 rows. Do you mean that you need to return only one value which is the average of these three rows?

Please provide: (1) queries to create the relevant table and to insert some sample data for the sake of the discussion and in order to reproduce the scenario. (2) In addition please explain what is the expected result according to the sample data.

0 Votes 0 ·

Your first query take average of all rows in the table and you second query returns only 3 rows.
• That is correct, I would like to perform both functions under 1 query
Do you mean that you need to return only one value which is the average of these three rows?
• Yes, I am going to query 3 rows of F1 data and take that average
Please provide: (1) queries to create the relevant table and to insert some sample data for the sake of the discussion and in order to reproduce the scenario.

 CREATE TABLE mytable (
             ID int,
             F1 varchar(50),
             lineselected varchar(50)
             )

 INSERT INTO [mytable] (ID, F1, lineselected)
    
 VALUES
 (1,12.123,'batphone'),
 (2,12.234,'batphone'),
 (3,12.143,'batphone')

(2) In addition please explain what is the expected result according to the sample data.
I am looking for the average of the last 3 entries:
In Excel:
ID F1
3 12.123
2 12.234
1 12.143

     AVERAGE    12.167




0 Votes 0 ·

1 Answer

pituach avatar image
0 Votes"
pituach answered pituach commented

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This worked!
Thank you!!

1 Vote 1 ·
pituach avatar image pituach ceramicprofiler ·

You are most welcome 138333-image.png


0 Votes 0 ·
image.png (796 B)