代码之家 › 专栏 › 技术社区 › user3127554

SQL:SELECT查询中间的SUM失败

group-by join sql

-1

user3127554 · 技术社区 · 7 年前

我有以下表格:

发票联

公司名称

发票行

发票ID
数量

我想要以下信息:公司名称和ID为1760的发票总额。

我使用以下查询:

SELECT i.*, sum(il.amount) as 'Total' 
FROM invoice i JOIN invoiceLine il on  i.invoiceID = il.InvoiceID 
WHERE i.InvoiceID = 1760

我认为这是个错误:

3 回复 | 直到 7 年前

1

2

Degan 7 年前

对于未聚合的每个列,都需要一个分组依据

SELECT i.invoiceID, i.companyName, sum(il.amount) as 'Total' 
FROM invoice i JOIN invoiceLine il on  i.invoiceID = il.InvoiceID 
WHERE i.InvoiceID = 1760
GROUP BY i.invoiceID, i.companyName

2

Ian-Fogelman 7 年前

SELECT i.InvoiceID,i.CompanyName, sum(il.amount) as 'Total' 
FROM invoice i JOIN invoiceLine il on  i.invoiceID = il.InvoiceID 
WHERE i.InvoiceID = 1760
group by i.InvoiceID,i.CompanyName

3

2

Gordon Linoff 7 年前

可以使用关联查询:

SELECT i.*,
       (SELCT sum(il.amount)
        FROM invoiceLine il 
        WHERE i.invoiceID = il.InvoiceID
       ) as Total
FROM invoice i 
WHERE i.InvoiceID = 1760;