How can I use aggregate function SUM on an alias c

问题:

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

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