Sum is too high

[JohnnyB]

Hello All,

I'm still a SQL newbie so I hope this question is easy. I have to sum some transactional data and do a report on it. I used the SUM() function and found out that my totals were too high. I did an experiment on my data and just pulled a months worth and checked the sums for a couple of data categories within the month. The sum for the second one I looked at where I used the sum function was higher than the sum of the individual records. Here are examples of my code, first with the SUM() function:

SET NOCOUNT ON
SELECT COM_Number,
        COM_Name, 
        Sum(TRA_Amount) as Amount, 
        Sum(ITR_Amount) AS Fraud, 
        CAST('01 '+ RIGHT(CONVERT(CHAR(11),TRA_TransactionDate,113),8) AS DATETIME) AS 'TransMonth',
        MerchantCategoryCode.MCC_Code, 
        MerchantCategoryCode.MCC_Name
    FROM ((((Transaction WITH (NOLOCK)
    INNER JOIN (Company WITH (NOLOCK) 
        INNER JOIN Account WITH (NOLOCK) ON Company.COM_CompanyId = Account.ACC_CompanyId) 
    ON Transaction.TRA_AccountId = Account.ACC_AccountId) 
        INNER JOIN Merchant WITH (NOLOCK) ON Transaction.TRA_MerchantId = Merchant.MER_MerchantId) 
            INNER JOIN TransactionCode WITH (NOLOCK) ON TRA_TransactionCodeId = TransactionCode.TCO_TransactionCodeId) 
                INNER JOIN MerchantCategoryCode WITH (NOLOCK) ON Merchant.MER_MerchantCategoryCodeId = MerchantCategoryCode.MCC_MerchantCategoryCodeId) 
                    LEFT JOIN IncidentTransaction WITH (NOLOCK) ON Transaction.TRA_MicroficheReferenceNumber = IncidentTransaction.ITR_ReferenceNumber
WHERE     (Company.COM_Number ='655')
    AND [TRA_TransactionDate] between '8/1/2009' and '8/31/2009' 
    AND TransactionCode.TCO_Code 
        In (
        '001',
        '006',
        '008',
        '031',
        '036',
        '038')
Group BY Company.COM_Number,
        Company.COM_Name, 
        Transaction.TRA_TransactionDate, 
        MerchantCategoryCode.MCC_Code, 
        MerchantCategoryCode.MCC_Name    

And here's the SQL that returns the individual transactions. I copied the results of both to excel and sorted by the MCC_Code and did totals for each group of codes. The totals were less for the following SQL and I believe this one is right:

SET NOCOUNT ON
SELECT Company.COM_Number,
        Company.COM_Name, 
        Transaction.TRA_Amount, 
                IncidentTransaction.ITR_Amount, 
        CAST('01 '+ RIGHT(CONVERT(CHAR(11),TRA_TransactionDate,113),8) AS DATETIME) AS 'TransMonth',
        MerchantCategoryCode.MCC_Code, 
        MerchantCategoryCode.MCC_Name
    FROM ((((Transaction WITH (NOLOCK)
    INNER JOIN (Company WITH (NOLOCK) 
        INNER JOIN Account WITH (NOLOCK) ON Company.COM_CompanyId = Account.ACC_CompanyId) 
    ON Transaction.TRA_AccountId = Account.ACC_AccountId) 
        INNER JOIN Merchant WITH (NOLOCK) ON Transaction.TRA_MerchantId = Merchant.MER_MerchantId) 
            INNER JOIN TransactionCode WITH (NOLOCK) ON Transaction.TRA_TransactionCodeId = TransactionCode.TCO_TransactionCodeId) 
                INNER JOIN MerchantCategoryCode WITH (NOLOCK) ON Merchant.MER_MerchantCategoryCodeId = MerchantCategoryCode.MCC_MerchantCategoryCodeId) 
                    LEFT JOIN IncidentTransaction WITH (NOLOCK) ON Transaction.TRA_MicroficheReferenceNumber = IncidentTransaction.ITR_ReferenceNumber
 
WHERE     (Company.COM_Number ='655')
    AND [TRA_TransactionDate] between '8/1/2009' and '8/31/2009' 
    AND TransactionCode.TCO_Code 
        In (
        '001',
        '006',
        '008',
        '031',
        '036',
        '038')
Group BY Company.COM_Number,
        Company.COM_Name, 
        Transaction.TRA_TransactionDate, 
        Transaction.TRA_Amount, 
        IncidentTransaction.ITR_Amount,  
        MerchantCategoryCode.MCC_Code, 
        MerchantCategoryCode.MCC_Name

My main question is, how do I get the totals for the results of first query with the SUM function to match the second one?

Thanks,

John

[code green]

I've not studied your query too closely,
but what is likely happening is there are multiple joins to the same table so figures are being counted more than once.

Test the query with '*' in place of all the SELECT fields and without a GROUP BY.
This will show exactly what is being added.

This can be fixed with an extra condition inside the ON clause

[NeoPa]

That's certainly good advice.

Another tip would be to use Common Table Expressions (CTEs) in your code. This just allows you to isolate elements of a complicated structure and deal with them more easily than looking through loads of parenthesised joins.

In your case I'd consider putting the items you need GROUPed within your CTE.

While developing your CTE it's a good idea to use :

WITH cteTest
AS
(
SELECT ...
FROM ...
GROUP BY ...
)
SELECT *
FROM   [cteTest]

Just to test that this part of the whole is working as you'd expect before building on it.

CTEs are little more than glorified subqueries, but they can be very useful for simplifying things for all that.

Good luck with your SQL :)