CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110083

SUMMING a conditional statement

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?

Upvotes: 5

Views: 1255

Answers (4)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

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.

Upvotes: 3

Andriy M
Andriy M

Reputation: 77657

Since there are only two alternatives, you could also use the IF() function:

SELECT SUM(sales * IF(currency IN ('jpy', 'aud'), 0.95, 1))
FROM combined

Another method would be to use implicit boolean conversion to 0 or 1 like this:

SELECT SUM(sales * (1 - currency IN ('jpy', 'aud') * 0.05))

The currency IN ('jpy', 'aud') boolean expression would implicitly convert to either 0 (when false) or 1 (when true), and that would result in either sales * (1 - 0) or sales * (1 - 0.05), accordingly.

Upvotes: 2

StuartLC
StuartLC

Reputation: 107237

I think Alex is just about right. I've assumed that you can't mix jpy and usd etc so will need to group by currency, viz

SELECT currency, 
       SUM(sales) * (1 - CASE WHEN currency IN ('jpy', 'aud') 
                            THEN .05 -- 5% Tax
                         ELSE 
                            0 -- No tax
                         END)
FROM
(
    SELECT 'aud' as currency, 10.00 as sales
    UNION 
    SELECT 'usd' as currency, 20.00 as sales
    UNION
    SELECT 'gbp' as currency, 30.00 as sales
    UNION 
    SELECT 'jpy' as currency, 40.00 as sales
) salesdata
GROUP BY currency

Returns

aud 9.50
gbp 30.00
jpy 38.00
usd 20.00

Which Deducts 5% off the jpy and aud totals

Upvotes: 1

Alex Kremer
Alex Kremer

Reputation: 128

select sum(sales) - case when (currency in ('jpy', 'aud') then (Sum(sales) * .05) else 0 end

(TSQL or mysql?)

Upvotes: 1

Related Questions