如何在别名列上使用聚合函数SUM？

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

发票ID,采购订单编号和dueDate重复显示。

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

TotalPrice是一个别名(它应该是单价,总价格是一个错误,所以假设它单价不是总价)

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.

TotalShippingPrice显示与InvoiceID / PONumber关联的运费,对于每个invoiceID / PONumber,将有单个运费。同样的规则适用于跟踪号码。

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

年份代表发票发送的年份(不用担心)。

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

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

My request is:

我的要求是:

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.

我需要删除invoiceID复制并且每个发票都有单价的总和,因此每个invoiceID / PONumber只有一个记录,单位价格总和。

For example:

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

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

So my question is:

所以我的问题是:

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

由于“UnitPrice”列是别名,我无法得到它的总和!我该怎么办?我想有psedu代码或关于如何做的想法......

---

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

2 个解决方案

#1

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

#2

Just a shot at it but if you put the whole select statement into the FROM clause you can then sum across the alias:

只需注意它,但如果将整个select语句放入FROM子句,则可以对别名求和:

SELECT (Going to have to include the fields here and group by them), SUM(TotalPrice) FROM
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

GROUP BY (all fields in the select except total price).