Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working

Question

I am not getting my head around this, and wondered if anyone may be able to help me with this.

I have 2 Tables called RES_DATA and INV_DATA

RES_DATA Contains my Customer as below

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

INV_DATA Contains their INVOICES 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 Which will give me the results as 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 to Add the tables and SUM Values of the INVOICES Table GROUPED BY the Customer Table but honestly think I am missing something. Can't even get close to the Results I need.

Answer 1

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.

Answer 2

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]