Question :
I have created a relation database structure below,
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
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 ;