Group By Help

Question

Post reply

Group By Help

dchelm

Valued Member

Points: 61
More actions
April 5, 2002 at 1:13 pm

#63925

I have a query below. I have several cloumns of Output and am trying to and a count column to the end.

If I leave in the where clause it is limiting my output to much even though I have the tables set up with outer joins. If I take out the where clause my result set is correct except the count is wrong.

Is there a way to limit the Count statement without limiting the rest of the result set?

Thanks in advance

Dave

SELECT TOP 100 PERCENT dbo.viewRegMain.RegID AS MainRegId, dbo.viewRegMain.MeetingDesc, dbo.viewRegMain.LastName AS MainLast,

dbo.viewRegMain.FirstName AS MainFirst, dbo.viewRegMain.RefID AS MainRefId, dbo.viewRegMain.State AS MainState,

dbo.viewRegMain.CompanyName as MainCompany, dbo.viewRegGuest.CompanyName AS GuestCompany,

dbo.viewRegMain.CountryName AS MainCountry, dbo.viewRegGuest.LastName AS GuestLast, dbo.viewRegGuest.FirstName AS GuestFirst,

dbo.viewRegGuest.RefID AS GuestRefId, dbo.viewRegGuest.State AS GuestState, dbo.viewRegGuest.CountryName AS GuestCountry,

dbo.viewRegGuest.MasterRegID AS GuestMasterId, dbo.viewRegGuest.RegID AS GuestRegId, dbo.viewRegMain.MeetingCode,

dbo.viewRegMain.DateCancelled AS MainCancDate, dbo.viewRegGuest.DateCancelled AS GuestCancDate,

dbo.viewRegMain.RegDesc AS MainRegDesc, dbo.viewRegGuest.RegDesc AS GuestRegDesc,

dbo.viewRegMain.AttendeeList AS MainAttendee, dbo.viewRegGuest.AttendeeList AS GuestAttendee,

dbo.viewRegMain.RegDate AS MainRegDate, dbo.viewRegGuest.RegDate AS GuestRegDate,

CASE WHEN dbo.viewRegMain.DateCancelled IS NULL

THEN 'Active' ELSE 'Cancelled' END AS RegStatus,

CASE WHEN dbo.viewRegGuest.RegID IS NOT NULL THEN

(CASE WHEN dbo.viewRegGuest.DateCancelled IS NULL

THEN 'Active' ELSE 'Cancelled' END)

ELSE

NULL

END AS GuestStatus,

COUNT(DISTINCT dbo.tblHotelReservations.HotelReservationID) AS HotelResCount

FROM dbo.viewRegMain LEFT OUTER JOIN

dbo.viewRegGuest ON dbo.viewRegMain.RegID = dbo.viewRegGuest.MasterRegID LEFT OUTER JOIN

dbo.tblHotelReservations ON dbo.viewRegMain.RegID = dbo.tblHotelReservations.RegID

/*

WHERE (dbo.tblHotelReservations.DateCancelled IS NULL)

*/

GROUP BY dbo.viewRegMain.RegID, dbo.viewRegMain.MeetingDesc, dbo.viewRegMain.LastName,

dbo.viewRegMain.FirstName, dbo.viewRegMain.RefID, dbo.viewRegMain.State,

dbo.viewRegMain.CompanyName, dbo.viewRegGuest.CompanyName,

dbo.viewRegMain.CountryName, dbo.viewRegGuest.LastName, dbo.viewRegGuest.FirstName,

dbo.viewRegGuest.RefID, dbo.viewRegGuest.State, dbo.viewRegGuest.CountryName,

dbo.viewRegGuest.MasterRegID, dbo.viewRegGuest.RegID, dbo.viewRegMain.MeetingCode,

dbo.viewRegMain.DateCancelled, dbo.viewRegGuest.DateCancelled,

dbo.viewRegMain.RegDesc, dbo.viewRegGuest.RegDesc,

dbo.viewRegMain.AttendeeList, dbo.viewRegGuest.AttendeeList,

dbo.viewRegMain.RegDate, dbo.viewRegGuest.RegDate,

CASE WHEN dbo.viewRegMain.DateCancelled IS NULL

THEN 'Active' ELSE 'Cancelled' END,

CASE WHEN dbo.viewRegGuest.RegID IS NOT NULL THEN

(CASE WHEN dbo.viewRegGuest.DateCancelled IS NULL

THEN 'Active' ELSE 'Cancelled' END)

ELSE

NULL

END

ORDER BY dbo.viewRegMain.LastName, dbo.viewRegMain.FirstName, dbo.viewRegGuest.LastName, dbo.viewRegGuest.FirstName