January 9, 2006 at 10:38 pm
hi,
I have following query
SELECT TOP 6 smu_reading
FROM LU_SMU_READING
WHERE (equipmentid_auto = 760)
ORDER BY date_reading DESC
which returns
3745.00
3520.00
3520.00
3520.00
3520.00
3520.00
i wanted to get the average of this record set so i used avg(smu_reading) but did not get any joy. I have tried using hints but didn't succeed.
if anybody can help??
thanks,
nuwan
January 9, 2006 at 10:41 pm
Do you just need the average or do you need both the average and the six values as well?
K. Brian Kelley
@kbriankelley
January 9, 2006 at 10:56 pm
i just want an average of the recordset.
nuwan
January 9, 2006 at 11:08 pm
If you need the order by and the top 6... you'll probably want to use a subquery. Something like:
SELECT SUM(smu_reading) Total
FROM (SELECT TOP 6 smu_reading
FROM LU_SMU_READING
WHERE (equipmentid_auto = 760)
ORDER BY date_reading DESC) A
K. Brian Kelley
@kbriankelley
January 10, 2006 at 1:02 pm
Yes, I don't think you can use TOP with the aggregate function like that. I believe bkelley was right except you were using the AVG function instead of SUM.
SELECT AVG(smu_reading) Total
FROM
(
SELECT TOP 6 smu_reading
FROM LU_SMU_READING
WHERE (equipmentid_auto = 760)
ORDER BY date_reading DESC
) A
January 10, 2006 at 1:07 pm
Oops. Yup. AVG instead of SUM.
K. Brian Kelley
@kbriankelley
January 15, 2006 at 1:56 pm
thanks guys.. I used a subquery and it worked well.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply