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.AuthorizationAmount) doubles the value when there is more than 1 guest record.

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

Open in new window


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

Open in new window


For each Registration.RegistrationID2, 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.
javierpdxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you have to first group for the first join, then group/join for the second table,...
select sq.RegistrationID2, sq.FirstName, sq.LastName
, COUNT(g.GuestRegID) AS Guests
FROM (  
          Select r.RegistrationID2, r.FirstName, r.LastName,  r.STUDENTID
              , SUM(t.AuthorizationAmount) as TotalAmountPaid  
           FROM Registration r
            LEFT JOIN CPSTransaction t ON r.STUDENTID = t.ApplicationIDPrimary 
          WHERE r.IsActive = 'True' 
             AND r.CommunicationPreference = 'Email'
             AND r.CommsSent='0' 
   GROUP BY  r.RegistrationID2, r.FirstName, r.LastName, r.STUDENTID
 ) sq
LEFT JOIN Guest g
   ON sq.StudentID = g.StudentIDAND 
GROUP BY sq.RegistrationID2, sq.FirstName, sq.LastName

Open in new window


see this article about using aliases:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
0
 
javierpdxAuthor Commented:
Thank you very much!
It worked great!

The link you provided did not work for me, but I will look for the article.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.