MrG MrG - 1 month ago 10
SQL Question

How to do conditional multiplication in SQL

The problem is I have two tables and have to calculate Total Revenue, which I figured out, but can't figure out how to apply condition. My attempt at it below. Thanks!

SELECT
(((SUM(i.ItemCost)) + ((SUM(i.ItemCost)) * j.MarginPercentage)) WHERE j.WorkFlowsStatus = 'Closed')
FROM Jobs AS j
INNER JOIN Items AS i
ON j.JobNumber = i.JobNumber
INNER JOIN Customers as c
ON j.CustomerID = c.CustomerID
WHERE c.CustomerID = '1235735'
GROUP BY c.CustomerName;

Answer

I like to do the calculation first, then do the sum over the values. The query without the sum gives you the details for each row and you can see the details of the values you are adding for each row just by taking out the grouping.

SELECT
    SUM(CASE
            WHEN j.WorkFlowsStatus = 'Closed' THEN i.ItemCost * j.MarginPercentage + i.ItemCost
            ELSE i.ItemCost
        END)
FROM Jobs AS j
    INNER JOIN Items AS i ON j.JobNumber = i.JobNumber
    INNER JOIN Customers as c ON j.CustomerID = c.CustomerID
WHERE c.CustomerID = '1235735'

If you get a value you think is wrong, just take out the SUM() and leave the CASE and add '*' and you can see all the detail records and figure out what is going wrong.

When just doing a sum over all entries, you don't need a group by. If you do use a group by, any fields that are not in an aggregate function need to be in the group by list. Since you are querying on a specific CustomerID you know what you should be getting. If you weren't doing that, grouping by customer name would be risky, think about what would happen if two customer rows ended up having the same name.