GROUP BY Problem in my Script

[baburk]

Hi,

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

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.

Thanks in advance

[Teme64]

Since you have two different Availability values, you'll get two rows no matter what you do. Drop the dbo.RoomRates.Overflow = 1 check.

Or use that query as a sub query:

SELECT DISTINCT dbo.RoomRates.RoomID, MIN(Availability) As Availability, SUM(RoomTotalPrice) AS RoomTotalPrice FROM (< your original query here >)