GROUP averages[resolved] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP averages[resolved]

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-26 : 09:56:52

I have a complicated query using multiple derviced tables, joins etc. One column is a calculated column. I would like to display an average of the calculated column for groups of rows but keep all the original rows and columns (so the average value will be shown beside each of the rows it represent the average of.
I can get group averages by copying query as another derived table, removing most of the columns and do grouping then join to the original query by group value. This is very cumbersome because original query is quite long. Is there any way to do this without repeating original query?
Here is a simplified version using one table to get a daily occupancy:

Select vw.*,Av_DailyOccupancy from (
SELECT [ID],[NumberPresent],[capacity],[Week_day],[AM_PM],
([NumberPresent]/[capacity]) as Occupancy
FROM dbo].[A]
) as vw

JOIN
(
SELECT Week_day
,avg([NumberPresent]/[capacity]) as Av_DailyOccupancy
FROM dbo].[A]
Group BY Week_day
) as Av
ON Av.Week_day = vw.Week_day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 10:04:21

[code]
Select vw.*,Av_DailyOccupancy from (
SELECT [ID],[NumberPresent],[capacity],[Week_day],[AM_PM],
([NumberPresent]/[capacity]) as Occupancy,
AVG([NumberPresent]*1.0/[capacity]) OVER (PARTITION BY Week_day) AS Av_DailyOccupancy
FROM dbo].[A]
) as vw
[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-26 : 10:58:11

Cheers - that's exactly what I wanted

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources