MrG - 4 months ago 34

SQL Question

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.