sql-server - 如何在 SQL 中为每组选择一行？

Question

我将使用几乎下表和数据，并添加 Net、GST 和 Gross 列：

CustomerPO| Net  | GST |  Gross
DS4284735   100    50     150
DS4284735   70     30     100

我的预期结果将是：

CustomerPO| Net  | GST |  Gross
DS4284735   170    80     250

我怎样才能做到这一点？

我的选择代码是：

SELECT dbo.Invoice.CustomerPO AS Reference, dbo.InvoiceDetails.ExtendedPrice AS Net, dbo.InvoiceDetails.TotalTaxes AS GST, 
                     dbo.InvoiceDetails.ExtendedPrice + dbo.InvoiceDetails.TotalTaxes AS Gross

我必须按所有内容分组吗？请注意，这两个在数据库表中具有不同的 InvoiceID

标签： sql-server

Answer 1

SELECT dbo.Invoice.CustomerPO AS Reference
, ISNULL(dbo.Invoice.CredInvoiceNo, dbo.Invoice.InvoiceNo)
 AS [Invoice Number]
, AVG(dbo.InvoiceDetails.ExtendedPrice) AS Net
, AVG(dbo.InvoiceDetails.TotalTaxes)  AS GST
, CONVERT(int, ROUND(SUM(dbo.InvoiceDetails.ExtendedPrice + dbo.InvoiceDetails.TotalTaxes), 0)) AS Gross
GROUP BY dbo.Invoice.InvoiceNo, dbo.Invoice.CustomerPO, ISNULL(dbo.Invoice.CredInvoiceNo, dbo.Invoice.InvoiceNo), dbo.Invoice.EntryDate