SUMMING a conditional statement

Question

I have the following query that gives me the sum of sales:

SELECT SUM(sales)
FROM 
    (SELECT sales FROM ...) combined

I now need to add a conditional subtraction based upon certain currencies that take out taxes, something like this, in pseudocode:

SELECT SUM(sales) - (SUM(sales) IF currency IN ('jpy', 'aud') * 0.05)
FROM 
    (SELECT sales FROM ...) combined

How would I create this conditional SUM or subquery?

Answer 1

You might apply factor while summing:

SELECT SUM(CASE WHEN currency IN ('jpy', 'aud') 
                THEN sales * 0.95
                ELSE sales
            END)
FROM combined

CASE has two forms. This one checks whether boolean expression given after WHEN evaluates to true. If it does, it returns sales scaled down by 5 percent; if it does not, returns expression after ELSE. You can combine WHEN...THEN pairs. The first one that evaluates to true returns expression after THEN.

You might have done this as you suggested in a question, by substracting TAX from JPY and AUD, as follows:

SELECT SUM(sales) 
     - ifnull (SUM(CASE WHEN currency IN ('jpy', 'aud') 
                        THEN sales * 0.05
                    END), 0)
FROM combined

But it would not help the query in any way.