Geek asks and answers

There is a conditional statement

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

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

Now I need to add a conditional deduction based on certain currencies that tax, something like this, in the pseudocode:

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

How can I create this conditional SUM or subquery?

+5
source share
4 answers

You can apply the coefficient when summing:

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

CASE has two forms. This checks to see if a logical expression is output after WHEN to evaluate to true. If this happens, it will reduce sales by 5 percent; if not, returns the expression after ELSE. You can combine WHEN ... THEN pairs. The first that evaluates the true return expression after THEN.

, , , TAX JPY AUD, :

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

.

+3

, IF():

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

0 1 :

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

currency IN ('jpy', 'aud') 0 ( false), 1 ( true), sales * (1 - 0) sales * (1 - 0.05).

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

(TSQL mysql?)

+1

, . , usd .., ,

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


aud 9.50
gbp 30.00
jpy 38.00
usd 20.00

5%

+1

More articles:


All Articles