Solved

TSQL join, sum, count

Posted on 2013-01-25
2
440 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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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