Trying to use INNER JOIN and GROUP BY SQL with SUM function does not work

Question

I do not understand what it is, and I thought if anyone could help me with this.

I have 2 tables called RES_DATA and INV_DATA

RES_DATA Contains my client below

 CUSTOMER ID | NAME 1, Robert 2, John 3, Peter 

INV_DATA Contains their MESSAGES as below

 INVOICE ID | CUSTOMER ID | AMOUNT 100, 1, £49.95 200, 1, £105.95 300, 2, £400.00 400, 3, £150.00 500, 1, £25.00 

I am trying to write a SELECT STATEMENT that will give me the results below.

 CUSTOMER ID | NAME | TOTAL AMOUNT 1, Robert, £180.90 2, John, £400.00 3, Peter, £150.00 

I think I need 2 INNER JOINS. Somehow adding tables and SUM values ​​to the INVOICES GROUPED by Customer Table table, but honestly, I missed something. I can’t even get closer to the results that I need.

Answer 1

That 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 vs SQL Server 2008: http://sqlfiddle.com/#!3/1cad5/1

Basically, what happens because of the connection, you get the same row in the "left" (ie from the RES_DATA table) for each row "on the right" (i.e. the INV_DATA table), which has the same meaning [CUSTOMER ID] . When you group only the columns on the left side, and then make the sum of only the [AMOUNT] column on the right side, it holds one row integer on the left side and sums the corresponding values ​​on the right side.

Answer 2

Two ways to do this ...

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] 

Answer 3

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]