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!
(((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.