Solved

TSQL join, sum, count

Posted on 2013-01-25
2
436 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 142

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now