尝试使用INNER JOIN和GROUP BY SQL与SUM函数，不工作

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

我有2个名为RES_DATA和INV_DATA的表

RES_DATA Contains my Customer as below

RES_DATA包含我的客户，如下所示

CUSTOMER ID | NAME

1, Robert
2, John
3, Peter

INV_DATA Contains their INVOICES as Below

INV_DATA包含下面的INVOICES

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.

我正在尝试编写一个SELECT语句，它将给我如下结果。

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.

我想我需要2个INNER JOINS以某种方式添加INVOICES表的表和SUM值BY GROUP BY Customer Table但老实说我认为我遗漏了一些东西。甚至无法接近我需要的结果。

3 个解决方案

#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

我使用SQL Fiddle对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.

基本上这里发生的是，由于连接，你在“左”（即从RES_DATA表）获得相同的行“右”（即INV_DATA表）上的每一行具有相同的[CUSTOMER ID]值。当您仅按左侧的列进行分组，然后从右侧只执行[AMOUNT]列的总和时，它会保持左侧的一行完整，并汇总右侧的匹配值。

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

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

SELECT * FROM RES_DATA内连接（SELECT [CUSTOMER ID]，sum（[TOTAL AMOUNT]）FROM INV_DATA group by [CUSTOMER ID]）T on RES_DATA。[CUSTOMER ID] = t。[CUSTOMER ID]

#1