MrG - 1 year ago 80
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;
``````

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download