CODEWITHSUNDEEP
sql
snapper
snapper

Reputation: 210

Returning the sum of a select statement SQL

I have created a SELECT statement: 

SELECT ARII2.Amount  
FROM 
    AR_Customer ARC2
    Inner JOIN AR_Customer_Site         ARCS2   On ARC2.Customer_Id = ARCS2.Customer_Id AND ARCS2.Customer_Id = ARC2.Customer_Id
    INNER JOIN AR_Customer_System       ARCSYS2  On ARCS2.Customer_Site_Id = ARCSYS2.Customer_Site_Id
    INNER JOIN AR_Branch                ARB2     ON ARB2.Branch_Id = ARC2.Branch_Id
    INNER JOIN AR_Invoice               ARIN2    ON ARIN2.Customer_Site_Id = ARCS2.Customer_Site_Id
    INNER JOIN AR_Invoice_Item          ARII2    ON ARII2.Invoice_Id = ARIN2.Invoice_Id   
    Inner JOIN SY_System                SYSY2   On ARCSYS2.System_Id = SYSY2.System_Id                     
WHERE 
    ARIN2.Invoice_Date > dateadd(year, -1, getdate())
    AND ARC2.Customer_Number = '300000'         
    AND ARII2.[Description] LIKE ('Warranty Credit')
    OR ARII2.[Description] = ('Warranty Credit T')
GROUP BY ARII2.Amount

that returns the following results. 

2031.00
1458.98
1272.50
620.00
160.00

My thought was that I could put a SUM around my Amount and it would return the desired value of 5542.48 (the total of the values).

SELECT 
    SUM(ARII2.Amount)  
FROM 
    AR_Customer ARC2
    Inner JOIN AR_Customer_Site         ARCS2   On ARC2.Customer_Id = ARCS2.Customer_Id AND ARCS2.Customer_Id = ARC2.Customer_Id
    INNER JOIN AR_Customer_System       ARCSYS2  On ARCS2.Customer_Site_Id = ARCSYS2.Customer_Site_Id
    INNER JOIN AR_Branch                ARB2     ON ARB2.Branch_Id = ARC2.Branch_Id
    INNER JOIN AR_Invoice               ARIN2    ON ARIN2.Customer_Site_Id = ARCS2.Customer_Site_Id
    INNER JOIN AR_Invoice_Item          ARII2    ON ARII2.Invoice_Id = ARIN2.Invoice_Id   
    Inner JOIN SY_System                SYSY2   On ARCSYS2.System_Id = SYSY2.System_Id                     
WHERE 
    ARIN2.Invoice_Date > dateadd(year, -1, getdate())
    AND ARC2.Customer_Number = '300000'         
    --AND ARIN2.Invoice_Number = '204686'
    AND ARII2.[Description] LIKE ('Warranty Credit')
    OR ARII2.[Description] = ('Warranty Credit T')
GROUP BY ARII2.Amount, ARII2.[Description]

Which returned the following results which are not what I am looking for.

-10155.00
-7294.90
-6362.50
-3100.00
-800.00

As always any help on this is GREATLY appreciated!

Upvotes: 0

Views: 29

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31795

You are getting multiple rows because of your GROUP BY. Remove that line, and you will only get the one total SUM.

Upvotes: 4

Related Questions