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.
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.