Group Partition by - 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 Partition by

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-27 : 07:04:34

I thought I had this but I still need a little help - please.

visakh gave me a solution which I would like extended:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118579

The original solution was to give a Daily average of a calculated column alongside the calculated column:
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

How can I keep this column but also have another 2 columns 'AV_AM_Occupancy' and 'AV_PM_Occupancy'
- the average of the same calculated column but for both the morning and the afternoon.
In the AM_PM column the value is either 'AM' or 'PM'

I used:
AVG(Sum(No_In_Room)*1.0/SUM(capacity)) OVER (PARTITION BY [AM_PM]) AS AM_PM_Occupancy
but this gives a AM/PM average for the whole week - I need to group these so that e.g. Monday AM Average and Tuesday AM Average are not the same

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-01-27 : 07:54:36

OVER(PARTITION BY CASE WHEN AM_PM='AM' THEN AV_AM_Occupancy ELSE AV_PM_Occupancy END)AS Av_DailyOccupancy

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-01-27 : 08:01:58

OVER(PARTITION BY [AM_PM],Week_day)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:10:40

[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,[AM_PM]) AS Av_DailyOccupancy
FROM dbo].[A]
) as vw
[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-28 : 05:06:18

Thanks for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:54:14

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources