Broblem with group by clause - 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

General SQL Server Forums

New to SQL Server Programming

Broblem with group by clause

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2011-09-22 : 07:02:56

Hi All,

Here is my Query..

SELECT MONTH(BOOKING_DATE) AS [Month],SUM(ROOM_NIGHT) * AVG_DAILY_RATE AS [Total Rooms Revenue],
ISNULL(SUM(CASE BOOKING_STATUS WHEN 'P2P' THEN ROOM_NIGHT END ) ,0 * AVG_DAILY_RATE) AS 'P2P',
ISNULL(SUM(CASE BOOKING_STATUS WHEN 'OPP' THEN ROOM_NIGHT END ) ,0) * AVG_DAILY_RATE AS 'Opp'
FROM STA_RP_ADMIN.GB_ROOMS_PROPERTY INNER JOIN
dbo.HOTEL ON STA_RP_ADMIN.GB_ROOMS_PROPERTY.PROPERTY_ID = dbo.HOTEL.PROPERTY_ID
INNER JOIN GB_ROOMS_DETAILS ON GB_ROOMS_DETAILS.FK_ROOMS_ID = GB_ROOMS_PROPERTY.ROOMS_ID
WHERE GB_ROOMS_PROPERTY.CREATE_DTME BETWEEN @TranStartDate AND @TranEndDate AND
(dbo.HOTEL.ID = @HotelID)
AND MONTH(BOOKING_DATE) BETWEEN @StartMonth and @ENDMonth
GROUP BY MONTH(BOOKING_DATE)

I don't want to group by AVG_DAILY_RATE.
But without adding AVG_DAILY_RATE to group by it's showing error:
Column AVG_DAILY_RATE is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Anyone have ant idea..
Thanks in advance..

Anuj Pratap Singh

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-09-22 : 07:12:26

you are using AVG_DAILY_RATE column with a aggregate function,so you cannot eliminate it from the group by