Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL join, sum, count

Posted on 2013-01-25
2
Medium Priority
?
450 Views
Last Modified: 2013-01-25
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.
0
Comment
Question by:javierpdx
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38819853
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
 

Author Closing Comment

by:javierpdx
ID: 38820880
Thank you very much!
It worked great!

The link you provided did not work for me, but I will look for the article.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question