How can I use aggregate function SUM on an alias column?_问答_开发者

How can I use aggregate function SUM on an alias column?

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

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

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

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.

For example:

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

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

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 &开发者_如何学编程gt; 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

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