Need help to group record in my query

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

Need help to group record in my query

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2011-09-20 : 05:37:38

Hi All,

I am working on a query to show result in group.
Here is my query..
SELECT MONTH(BOOKING_DATE) AS [Month],COUNT(DISTINCT(BOOKING_ID)) AS [Total Bookings],BOOKING_STATUS,
CASE BOOKING_STATUS WHEN 'DEF' THEN COUNT(DISTINCT BOOKING_ID) ELSE '0' END AS '# Bookings DEF' FROM STA_RP_ADMIN.GB_ROOMS_DETAILS INNER JOIN
STA_RP_ADMIN.GB_ROOMS_PROPERTY ON
STA_RP_ADMIN.GB_ROOMS_DETAILS.FK_ROOMS_ID = STA_RP_ADMIN.GB_ROOMS_PROPERTY.ROOMS_ID INNER JOIN
dbo.HOTEL ON STA_RP_ADMIN.GB_ROOMS_PROPERTY.PROPERTY_ID = dbo.HOTEL.PROPERTY_ID
WHERE (dbo.HOTEL.ID = @HotelID)
AND MONTH(BOOKING_DATE) BETWEEN 1 and 2
GROUP BY MONTH(BOOKING_DATE),BOOKING_STATUS

It's showing output like:

Month Total Bookings BOOKING_STATUS # Bookings DEF
1 200 CXL 25
2 100 CXL 25
1 200 ALL 25
2 100 ALL 25
1 200 P1P 25
2 100 P1p 25

I want to group it by month, so result should be like

Month Total Bookings # Bookings DEF
1 600 75
2 300 75

i dont't want to group by status.
but sqlserver not allow me to group by month only.
I have to add status in group by.

anyone have any idea about this.
Thanks in advance..

Anuj Pratap Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 05:45:52

[code]SELECT MONTH(BOOKING_DATE) AS [Month],COUNT(DISTINCT(BOOKING_ID)) AS [Total Bookings],COUNT(DISTINCT CASE BOOKING_STATUS WHEN 'DEF' THEN BOOKING_ID ELSE NULL END ) AS '# Bookings DEF' FROM STA_RP_ADMIN.GB_ROOMS_DETAILS INNER JOIN
STA_RP_ADMIN.GB_ROOMS_PROPERTY ON
STA_RP_ADMIN.GB_ROOMS_DETAILS.FK_ROOMS_ID = STA_RP_ADMIN.GB_ROOMS_PROPERTY.ROOMS_ID INNER JOIN
dbo.HOTEL ON STA_RP_ADMIN.GB_ROOMS_PROPERTY.PROPERTY_ID = dbo.HOTEL.PROPERTY_ID
WHERE (dbo.HOTEL.ID = @HotelID)
AND MONTH(BOOKING_DATE) BETWEEN 1 and 2
GROUP BY MONTH(BOOKING_DATE)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anujpratap84
Starting Member

45 Posts

Posted - 2011-09-22 : 06:59:02

Thanks a lot..
It's working.....

quote:

Originally posted by visakh16

SELECT MONTH(BOOKING_DATE) AS [Month],COUNT(DISTINCT(BOOKING_ID)) AS [Total Bookings],COUNT(DISTINCT CASE BOOKING_STATUS WHEN 'DEF' THEN BOOKING_ID ELSE NULL END ) AS '# Bookings DEF' FROM STA_RP_ADMIN.GB_ROOMS_DETAILS INNER JOIN
STA_RP_ADMIN.GB_ROOMS_PROPERTY ON 
STA_RP_ADMIN.GB_ROOMS_DETAILS.FK_ROOMS_ID = STA_RP_ADMIN.GB_ROOMS_PROPERTY.ROOMS_ID INNER JOIN
dbo.HOTEL ON STA_RP_ADMIN.GB_ROOMS_PROPERTY.PROPERTY_ID = dbo.HOTEL.PROPERTY_ID
WHERE (dbo.HOTEL.ID = @HotelID)
AND MONTH(BOOKING_DATE) BETWEEN 1 and 2
GROUP BY MONTH(BOOKING_DATE)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Anuj Pratap Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 11:47:44

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources