I want to sum up a value only if another dependent is not 0 and then roll up the value after that.
Looking for contribution of team A to be 115 (75+40) and excludes -28 as it has 0 sales.
This is the original table.
| Team | Sales | Cost | Contribution |
|---|---|---|---|
| A | 100 | 25 | 75 |
| A | 50 | 10 | 40 |
| A | 0 | 28 | -28 |
| B | 80 | 40 | 40 |
| B | 0 | 15 | -15 |
my Query is
SELECT Team,
CASE
WHEN SUM(Sales) = 0 then 0 ELSE SUM(Contribution) END as Contribution
FROM Table
GROUP BY Team;
i tried this and got 87 instead of 115 for Team A that i am looking for.
| Team | Contribution |
|---|---|
| A | 115 |
| B | 40 |
2
If you really only want to select the Team and the Contribution and no other columns, you don’t need a conditional aggregation.
In this case, just build the sum and add a WHERE clause to only fetch those rows with a Sale different to zero:
SELECT
Team,
SUM(Contribution) AS Contribution
FROM yourtable
WHERE Sales <> 0
GROUP BY Team;
If you need further columns, your solution is almost correct, but you need to write SUM(CASE WHEN...) rather than CASE WHEN SUM....
Thus you will only exclude those rows having Sales = 0 from the sum of Contribution.
Otherwise, you will never exclude any rows except when the total of Sales is 0 and in this case, you will set total Contribution to 0.
Your query changed as described would be:
SELECT
Team,
SUM(CASE WHEN Sales = 0 THEN 0 ELSE Contribution END) AS Contribution
FROM yourtable
GROUP BY Team;
Note: You can invert the condition and simplify above query, then you don’t need the ELSE clause:
SELECT
Team,
SUM(CASE WHEN Sales <> 0 THEN Contribution END) AS Contribution
FROM yourtable
GROUP BY Team;
See this sample fiddle with your data.
The previous answers already answer the question but let me add one more tip:
This way of aggregating (let’s call it conditional aggregate) can also be useful to count records that match a condition. For example if you are not interested in the contribution value just want to know how many rows are there where sales > 0:
SELECT Team,
SUM(CASE WHEN Sales > 0 THEN 1 ELSE 0 END) CountOfSales
FROM yourtable
GROUP BY Team
Of course if this is the only data you need you can just put the condition in the WHERE section but if you want multiple such conditional aggregates or you want to see the Team in the result that has no records matching the condition (with a 0 result), this SUM acting as a COUNT could be useful.
1