SQL Server 2008 R2 – How to Get the SUM of Custom Query

sql serversql-server-2008-r2

I have created a relation database structure below,
enter image description here

from this relational table i want to get the SUM of all data, below my query,

SELECT ((BASE_InvoiceLine.Price - IIf(SUM(BASE_Expense.Amount) IS NULL, BASE_PurchaseLine.Price, (BASE_PurchaseLine.Price + SUM(BASE_Expense.Amount)))) + BASE_PurchaseLine.Recycle) AS ProfitRecycle
FROM BASE_PurchaseLine INNER JOIN BASE_InvoiceLine ON BASE_PurchaseLine.PurchaseLineId = BASE_InvoiceLine.PurchaseLineId AND (BASE_PurchaseLine.IsDelete = 0) INNER JOIN BASE_Invoice ON BASE_InvoiceLine.InvoiceId = BASE_Invoice.InvoiceId AND (BASE_Invoice.IsDelete = 0) AND (BASE_InvoiceLine.IsDelete = 0) LEFT OUTER JOIN BASE_Expense ON BASE_PurchaseLine.PurchaseLineId = BASE_Expense.PurchaseLineId AND (BASE_Expense.IsDelete = 0)
GROUP BY BASE_PurchaseLine.Price, BASE_InvoiceLine.Price, BASE_PurchaseLine.Recycle

Above query display all the values only.. problem is how to get the Sum of this Query result,

Thanks

Best Answer

The easiest is to wrap your query in a derived table or CTE and then do SUM :

SELECT sum(ProfitRecycle) AS SumProfitRecyxle
FROM
    (
    -- 
    -- your query here
    --
    ) AS dv ;
Related Question