如何在别名列上使用聚合函数 SUM? [英] How can I use aggregate function SUM on an alias column?

问题描述

发票 ID、采购订单编号和到期日期以重复形式显示.

Invoice ID, PO Number and dueDate are shown in duplicates.

TotalPrice 是别名(应该是 Unit Price，总价是错误的，所以假设它是 Unit Price 不是总价)

TotalPrice is an alias (It should be Unit Price, total price is a mistake, so assume it Unit Price not total price)

TotalShippingPrice 显示与 InvoiceID/PONumber 相关联的运费，对于每个 invoiceID/PONumber，都有一个运费.同样的规则适用于追踪号码.

TotalShippingPrice shows the shipping price that was associated with the InvoiceID/PONumber, for every invoiceID/PONumber there will be single shipping price. Same rule applied to tracking number.

Year 表示这张发票的发送年份(不用担心).

Year represents what year this invoice was sent (don't worry about it).

isTaxPaid 表示是否以单价支付税款(不用担心)

isTaxPaid represents whether a tax was paid in Unit Price or not (don't worry about it)

我的要求是:

我需要删除invoiceID重复并获得每张发票的单价总和，所以每个invoiceID/PONumber应该只有一个单价总和的记录.

I need to have the remove invoiceID duplicate and have the sum of unit price for every invoice, so there should be only one record of every invoiceID/PONumber with sum of unit prices.

例如:

30463 - 903315 - 无论到期日 - 368 (92 + 276) - ----- (trackingNumber) - 2010 - 0(已缴税)

30463 - 903315 - whatever due date - 368 (92 + 276) - ----- (trackingNumber) - 2010 - 0 (tax paid)

所以我的问题是:

由于UnitPrice"列是别名，我无法得到它的总和！我该怎么办?我想要psedu代码或关于如何做的想法......

Since "UnitPrice" column is an alias, i can not get the sum of it! What should i do? I would like to have the psedu-code or the idea on how to do it...

如果你想看我的查询，这里是(警告它看起来很可怕而且写得很糟糕，需要稍后调整):

In case you want to see my query, here it is (warning it looks scary and awfully written, need to tuned later on):

SELECT     CustomerInvoice.cuInvoiceID, CustomerQuote.PONumber, CustomerInvoice.dueDate, 
           CASE
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND CustomerQuoteProducts.isTaxPaid > 0 THEN SUM(((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) * CustomerQuoteProducts.qty)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND CustomerQuoteProducts.isTaxPaid > 0 THEN SUM((CustomerQuoteProducts.unitPrice * 1.15) * 1.15)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND CustomerQuoteProducts.isTaxPaid > 0 THEN SUM((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty) * 1.15) 
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND CustomerQuoteProducts.isTaxPaid > 0 THEN SUM(CustomerQuoteProducts.unitPrice * 1.15)
                 WHEN (SELECT Count(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND CustomerQuoteProducts.isTaxPaid > 0 THEN SUM((CustomerQuoteProducts.unitPrice * 1.15) * CustomerQuoteProducts.qty)
                 WHEN (SELECT Count(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND CustomerQuoteProducts.isTaxPaid > 0 THEN SUM(CustomerQuoteProducts.unitPrice * 1.15)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND CustomerQuoteProducts.isTaxPaid <= 0 THEN SUM(((CustomerQuoteProducts.unitPrice * 1.15)) * CustomerQuoteProducts.qty)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND CustomerQuoteProducts.isTaxPaid <= 0 THEN SUM((CustomerQuoteProducts.unitPrice * 1.15))
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND CustomerQuoteProducts.isTaxPaid <= 0 THEN SUM((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) 
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND CustomerQuoteProducts.isTaxPaid <= 0 THEN SUM(CustomerQuoteProducts.unitPrice)
                 WHEN (SELECT Count(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND CustomerQuoteProducts.isTaxPaid <= 0 THEN SUM((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty))
                 WHEN (SELECT Count(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND CustomerQuoteProducts.isTaxPaid <= 0 THEN SUM(CustomerQuoteProducts.unitPrice)
           END AS "TotalPrice",
           CASE WHEN row_number() OVER (partition BY CustomerInvoice.cuInvoiceId ORDER BY newid()) = 1 
                THEN (
                          CASE
                              WHEN CustomerShipping.isTaxPaid > 0 THEN SUM(CustomerShipping.shippingPrice * 1.15)
                              WHEN CustomerShipping.isTaxPaid <= 0 THEN SUM(CustomerShipping.shippingPrice)
                          END
                      )
           END AS "TotalShippingPrice",
           CASE WHEN row_number() OVER (partition BY CustomerInvoice.cuInvoiceId ORDER BY newid()) = 1 
                THEN CustomerShipping.trackingNumber
           END AS "trackingNumber",
           DATEPART(year, CustomerInvDetail.sentDate) AS Year, CustomerQuoteProducts.isTaxPaid
FROM         CustomerInvoice INNER JOIN
                      CustomerInvDetail ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID INNER JOIN
                      CustomerQuote ON CustomerQuote.customerQuoteID = CustomerInvoice.customerQuoteID INNER JOIN
                      CustomerQuoteProducts ON CustomerQuoteProducts.customerQuoteID = CustomerQuote.customerQuoteID INNER JOIN
                      CustomerShipping ON CustomerShipping.customerQuoteID = CustomerInvoice.customerQuoteID INNER JOIN
                      Customer ON Customer.customerID = CustomerQuote.customerID
WHERE     (DATEPART(year, CustomerInvDetail.sentDate) BETWEEN 1999 AND 2999) AND (Customer.customerID = 500)
GROUP BY CustomerInvDetail.sentDate, CustomerInvoice.cuInvoiceID, CustomerShipping.shippingPrice, CustomerInvoice.dueDate, CustomerQuote.PONumber, CustomerQuoteProducts.qty, CustomerQuoteProducts.ProductID, CustomerQuoteProducts.unitPrice, CustomerQuoteProducts.isTaxPaid, CustomerShipping.isTaxPaid, CustomerShipping.trackingNumber

推荐答案

您可以在原件周围放置另一个查询以生成总和:

You could place another query around your original to produce the sum:

SELECT x.cuInvoiceID, x.PONumber, x.DueDate, sum(x.TotalPrice), x.trackingNumber, x.Year, x.isTaxPaid
    FROM (<YourOriginalQuery>) x
    GROUP BY x.cuInvoiceID, x.PONumber, x.DueDate, x.trackingNumber, x.Year, x.isTaxPaid

这篇关于如何在别名列上使用聚合函数 SUM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！