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,


1 Answer 1


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.