April 5, 2002 at 1:13 pm
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
April 8, 2002 at 7:37 pm
(Select COUNT(DISTINCT dbo.tblHotelReservations.HotelReservationID)
WHERE dbo.tblHotelReservations.DateCancelled IS NULL)
AS HotelResCount
April 9, 2002 at 9:15 am
Thank You, works great
quote:
(Select COUNT(DISTINCT dbo.tblHotelReservations.HotelReservationID)WHERE dbo.tblHotelReservations.DateCancelled IS NULL)
AS HotelResCount
April 10, 2002 at 8:48 am
Oh btw..
If your only aggregate is the count
and you use the subquery posted by Deuce,
then you can get rid of your 'Group by'.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply