Task9_Past Expenses_Exam_27June2020

-- Task 9 Past Expenses
SELECT j.JobId, ISNULL(SUM(op.Quantity * p.Price), 0) AS Total
    FROM Jobs AS j
    LEFT JOIN Orders AS o ON o.JobId = j.JobId
    LEFT JOIN OrderParts AS op ON op.OrderId = o.OrderId
    LEFT JOIN Parts AS p ON op.PartId = p.PartId
    WHERE j.[Status] = 'Finished'
    GROUP BY j.JobId
    ORDER BY Total DESC,
                j.JobId ASC