javierpdx
asked on
TSQL join, sum, count
I have a sql statement that I'm using and am trying to add one additional column that counts GuestRegID from table Guest, joined by StudentID. The problem is that when I add it, the SUM(CPSTransaction.Authori zationAmou nt) doubles the value when there is more than 1 guest record.
This add's the sum correctly.
When I add the join and COUNT(Guest.GuestRegID) AS Guests it doubles the value.
For each Registration.RegistrationI D2, I only want to show once, and want to show the count of the GuestRegID from the Guest table joined by Student ID.
I've tried several different scenarios but am needing some help. What am I missing?
Thank you.
This add's the sum correctly.
Select Registration.RegistrationID2, Registration.FirstName, Registration.LastName, SUM(CPSTransaction.AuthorizationAmount) as TotalAmountPaid
FROM Registration LEFT JOIN CPSTransaction ON Registration.STUDENTID = ApplicationIDPrimary
WHERE Registration.IsActive = 'True' AND (CommunicationPreference = 'Email') AND (CommsSent='0')
GROUP BY Registration.RegistrationID2, Registration.FirstName, Registration.LastName
When I add the join and COUNT(Guest.GuestRegID) AS Guests it doubles the value.
Select Registration.RegistrationID2, Registration.FirstName, Registration.LastName, SUM(CPSTransaction.AuthorizationAmount) as TotalAmountPaid, COUNT(Guest.GuestRegID) AS Guests
FROM Registration LEFT JOIN CPSTransaction ON Registration.STUDENTID = ApplicationIDPrimary LEFT JOIN Guest ON CPSTransaction.ApplicationIDPrimary = Guest.StudentID
WHERE Registration.IsActive = 'True' AND (CommunicationPreference = 'Email') AND (CommsSent='0')
GROUP BY Registration.RegistrationID2, Registration.FirstName, Registration.LastName
For each Registration.RegistrationI
I've tried several different scenarios but am needing some help. What am I missing?
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked great!
The link you provided did not work for me, but I will look for the article.