Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working
Two ways to do it...
GROUP BY
SELECT RES.[CUSTOMER ID], RES,NAME, SUM(INV.AMOUNT) AS [TOTAL AMOUNT]
FROM RES_DATA RES
JOIN INV_DATA INV ON RES.[CUSTOMER ID] INV.[CUSTOMER ID]
GROUP BY RES.[CUSTOMER ID], RES,NAME
OVER
SELECT RES.[CUSTOMER ID], RES,NAME,
SUM(INV.AMOUNT) OVER (PARTITION RES.[CUSTOMER ID]) AS [TOTAL AMOUNT]
FROM RES_DATA RES
JOIN INV_DATA INV ON RES.[CUSTOMER ID] INV.[CUSTOMER ID]
Use subquery
SELECT * FROM RES_DATA inner join (SELECT [CUSTOMER ID], sum([TOTAL AMOUNT]) FROM INV_DATA group by [CUSTOMER ID]) T on RES_DATA.[CUSTOMER ID] = t.[CUSTOMER ID]
This should work.
SELECT a.[CUSTOMER ID], a.[NAME], SUM(b.[AMOUNT]) AS [TOTAL AMOUNT]
FROM RES_DATA a INNER JOIN INV_DATA b
ON a.[CUSTOMER ID]=b.[CUSTOMER ID]
GROUP BY a.[CUSTOMER ID], a.[NAME]
I tested it with SQL Fiddle against SQL Server 2008: http://sqlfiddle.com/#!3/1cad5/1
Basically what's happening here is that, because of the join, you are getting the same row on the "left" (i.e. from the RES_DATA
table) for every row on the "right" (i.e. the INV_DATA
table) that has the same [CUSTOMER ID]
value. When you group by just the columns on the left side, and then do a sum of just the [AMOUNT]
column from the right side, it keeps the one row intact from the left side, and sums up the matching values from the right side.