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

sql select join group-by inner-join

166,265

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

Solution 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]

Solution 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]

166,265

Rob4236

Updated on June 28, 2021

Comments

Rob4236 almost 3 years
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.
- rory.ap about 10 years
  
  what kind of SQL? SQL Server?
Rob4236 about 10 years

Thank you so much roryap that worked first time. Now to Understand this!
Hogan about 10 years

This answer will have O(n^2) performance, as op. to a group by or over which will have O(n)
rory.ap about 10 years

@user3526836 -- You're welcome. I've updated my answer to make it clearer what's going on. Hope it helps.
Hola Soy Edu Feliz Navidad about 10 years

Indeed, but he was looking for a result, not for performance.
molamola almost 6 years

The reason why a.[NAME] is also included is tuples which have those attributes are repeated. Am I right?
user890332 almost 5 years

This answer works well when you join within the same table to get a group by within some column in the table for each line in the table.
chindirala sampath kumar over 4 years

This solution has serious performance issues. Please do not consider this solution.
Sade over 4 years

How would one optimize the solution, if performance is a factor?
Connor Low almost 3 years

Please explain your code (even code comments are helpful).