GROUP BY Problem in my Script - 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 BY Problem in my Script

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-11-01 : 03:36:31

SELECT DISTINCT dbo.RoomRates.RoomID,
CASE
WHEN (dbo.RoomRates.Overflow = 1
AND MIN(dbo.RoomRates.Availability) <= 5)
THEN 5
ELSE MIN(dbo.RoomRates.Availability)
END AS Availability,
SUM( dbo.RoomRates.Price ) AS RoomTotalPrice
FROM dbo.RoomRates
WHERE dbo.RoomRates.[Date] BETWEEN @FromDate AND @ToDate
GROUP BY dbo.RoomRates.RoomID ,
dbo.RoomRates.Overflow

Input 1

roomid Date AvailableRooms RoomPrice FreeSale
100 01/12/08 FA 0 10 1
100 02/12/08 FA 8 10 0
100 03/12/08 FA 4 10 0

Output(needed output)

roomid Availability RoomTotalPrice
100 4 30

but what we are getting

roomid Availability RoomTotalPrice
100 5 10
100 4 20

Since Overflow data is different it groups according to Overflow data.

so we are receiving 2 records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 07:08:47

may be this

SELECT dbo.RoomRates.RoomID,
MIN(CASE
WHEN dbo.RoomRates.Overflow = 1
AND dbo.RoomRates.Availability <= 5
THEN 5
ELSE dbo.RoomRates.Availability
END) AS Availability,
SUM( dbo.RoomRates.Price ) AS RoomTotalPrice
FROM dbo.RoomRates
WHERE dbo.RoomRates.[Date] BETWEEN @FromDate AND @ToDate
GROUP BY dbo.RoomRates.RoomID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources