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

Solution 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
    GROUPBY x.cuInvoiceID, x.PONumber, x.DueDate, x.trackingNumber, x.Year, x.isTaxPaid

Solution 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 andgroupby them), SUM(TotalPrice) FROMSELECT    CustomerInvoice.cuInvoiceID, CustomerQuote.PONumber, CustomerInvoice.dueDate, 
           CASEWHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =1AND CustomerQuoteProducts.qty >0AND CustomerQuoteProducts.isTaxPaid >0THENSUM(((CustomerQuoteProducts.unitPrice *1.15) *1.15) * CustomerQuoteProducts.qty)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =1AND CustomerQuoteProducts.qty <=0AND CustomerQuoteProducts.isTaxPaid >0THENSUM((CustomerQuoteProducts.unitPrice *1.15) *1.15)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty >0AND CustomerQuoteProducts.isTaxPaid >0THENSUM((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty) *1.15) 
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty <=0AND CustomerQuoteProducts.isTaxPaid >0THENSUM(CustomerQuoteProducts.unitPrice *1.15)
                 WHEN (SELECTCount(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty >0AND CustomerQuoteProducts.isTaxPaid >0THENSUM((CustomerQuoteProducts.unitPrice *1.15) * CustomerQuoteProducts.qty)
                 WHEN (SELECTCount(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty <=0AND CustomerQuoteProducts.isTaxPaid >0THENSUM(CustomerQuoteProducts.unitPrice *1.15)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =1AND CustomerQuoteProducts.qty >0AND CustomerQuoteProducts.isTaxPaid <=0THENSUM(((CustomerQuoteProducts.unitPrice *1.15)) * CustomerQuoteProducts.qty)
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =1AND CustomerQuoteProducts.qty <=0AND CustomerQuoteProducts.isTaxPaid <=0THENSUM((CustomerQuoteProducts.unitPrice *1.15))
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty >0AND CustomerQuoteProducts.isTaxPaid <=0THENSUM((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) 
                 WHEN (SELECT isTaxPaid FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty <=0AND CustomerQuoteProducts.isTaxPaid <=0THENSUM(CustomerQuoteProducts.unitPrice)
                 WHEN (SELECTCount(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty >0AND CustomerQuoteProducts.isTaxPaid <=0THENSUM((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty))
                 WHEN (SELECTCount(isTaxPaid) FROM SupplierQuoteProducts WHERE productID = CustomerQuoteProducts.ProductID) =0AND CustomerQuoteProducts.qty <=0AND CustomerQuoteProducts.isTaxPaid <=0THENSUM(CustomerQuoteProducts.unitPrice)
           ENDAS "TotalPrice",
           CASEWHENrow_number() OVER (partitionBY CustomerInvoice.cuInvoiceId ORDERBY newid()) =1THEN (
                          CASEWHEN CustomerShipping.isTaxPaid >0THENSUM(CustomerShipping.shippingPrice *1.15)
                              WHEN CustomerShipping.isTaxPaid <=0THENSUM(CustomerShipping.shippingPrice)
                          END
                      )
           ENDAS "TotalShippingPrice",
           CASEWHENrow_number() OVER (partitionBY CustomerInvoice.cuInvoiceId ORDERBY newid()) =1THEN CustomerShipping.trackingNumber
           ENDAS "trackingNumber",
           DATEPART(year, CustomerInvDetail.sentDate) ASYear, CustomerQuoteProducts.isTaxPaid
FROM         CustomerInvoice INNERJOIN
                      CustomerInvDetail ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID INNERJOIN
                      CustomerQuote ON CustomerQuote.customerQuoteID = CustomerInvoice.customerQuoteID INNERJOIN
                      CustomerQuoteProducts ON CustomerQuoteProducts.customerQuoteID = CustomerQuote.customerQuoteID INNERJOIN
                      CustomerShipping ON CustomerShipping.customerQuoteID = CustomerInvoice.customerQuoteID INNERJOIN
                      Customer ON Customer.customerID = CustomerQuote.customerID
WHERE     (DATEPART(year, CustomerInvDetail.sentDate) BETWEEN1999AND2999) AND (Customer.customerID =500)
GROUPBY CustomerInvDetail.sentDate, CustomerInvoice.cuInvoiceID, CustomerShipping.shippingPrice, CustomerInvoice.dueDate, CustomerQuote.PONumber, CustomerQuoteProducts.qty, CustomerQuoteProducts.ProductID, CustomerQuoteProducts.unitPrice, CustomerQuoteProducts.isTaxPaid, CustomerShipping.isTaxPaid, CustomerShipping.trackingNumber

GROUPBY (all fields in the selectexcept total price).