October 16, 2005 at 12:09 pm
select avg
(select sum(duration) duration
from cutlog
group by datepart(dy,date))
from ...............
As you can see, I am trying to calculate an average of the values returned by subquery. But what "table" should I select from. My guess is it's some temporary table that stores the results of subquery, but I don't know how to do it in SQLServer.
Thank you.
October 16, 2005 at 12:51 pm
select avg(DurationSummed.duration)
from
(select sum(duration) duration
from cutlog group by datepart(dy,date))
) as DurationSummed
?
October 16, 2005 at 2:10 pm
Thanks!
October 20, 2005 at 5:31 am
Is a subquery needed
Select thedate, avg(duration), sum(duration)
From table
Group By thedate
If you have multiple runs a day use:
Convert(DateTime, Convert(VarChar, thedate, 101))
Which converts the date 01/01/2005 09:00:00.00 to 01/01/2005 and then convert it back to a date for sorting with a date of 01/01/2005 00:00:00.000 and all runs fall on the same day for the sum and avg of runs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply