CODEWITHSUNDEEP
mysqlsqlif-statement
eduardo0
eduardo0

Reputation: 131

Simple If Else statement in sql

I'm trying to get only the rows that are in USD and EUR from a table with more currencies.

I thought the code should be something like:

SELECT IF(CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY='USD',1,0) OR IF(CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY='EUR',1,0
FROM CONTRACTS_IN_DIFFERENT_CURRENCIES

But I know this is not how it should be. I would like the table to be something like a column of USD and EUR

Upvotes: 0

Views: 126

Answers (5)

outis nihil
outis nihil

Reputation: 736

It looks as though you're trying to use Excel's IF(condition, true_response, false_response) syntax. The equivalent in T-SQL is the CASE WHEN THEN ELSE END syntax:

SELECT 
    CASE 
        WHEN CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY ='USD' THEN 1 
        WHEN CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY='EUR' THEN 1
        ELSE 0
    END 
FROM 
    CONTRACTS_IN_DIFFERENT_CURRENCIES

This will work with more complex queries than the example you're giving us. Another way of doing it, if you have a number of possible values for the same field that will return the same response, would be 

SELECT 
    CASE 
        WHEN CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY IN ('USD','EUR') THEN 1
        ELSE 0
    END 
FROM 
    CONTRACTS_IN_DIFFERENT_CURRENCIES

However, that is not the right syntax to use to get just the rows with certain currencies; the previous answer with 

SELECT *     
FROM 
    CONTRACTS_IN_DIFFERENT_CURRENCIES
WHERE 
    CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY IN ('USD','EUR')

would work best for that.

Upvotes: 1

Patrick Q
Patrick Q

Reputation: 6393

If you want the count of the number of contracts in each of those currencies with each count as a column (which seems to be indicated by one of the comments in your question), then use this… 

SELECT SUM(CURRENCY = 'USD') AS usdContracts, 
    SUM(CURRENCY = 'EUR') AS eurContracts
FROM CONTRACTS_IN_DIFFERENT_CURRENCIES

If you want the count of the number of contracts in each of those currencies with each count as a row with the count in one column and the currency abbreviation in another, then use this… 

SELECT COUNT(*) AS numContracts, CURRENCY AS currency
FROM CONTRACTS_IN_DIFFERENT_CURRENCIES
WHERE CURRENCY = 'USD' OR CURRENCY = 'EUR'
GROUP BY CURRENCY;

Upvotes: 0

IEatBagels
IEatBagels

Reputation: 843

The preceding answers are good in my opinion, but let's say you would have alot of currencies, you could do 

SELECT * FROM CONTRACTS_IN_DIFFERENT_CURRENCIES WHERE CURRENCY IN  ("USD","EUR")

Upvotes: 0

Steen Schütt
Steen Schütt

Reputation: 1504

You need to use a WHERE clause.

As far as I can tell you're looking for a query along the lines of

SELECT * FROM `CONTRACTS_IN_DIFFERENT_CURRENCIES` WHERE `CURRENCY` = "USD" OR `CURRENCY` = "EUR"

Upvotes: 0

Tigran
Tigran

Reputation: 1057

SELECT * 
FROM CONTRACTS_IN_DIFFERENT_CURRENCIES WHERE
CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY='USD' OR CONTRACTS_IN_DIFFERENT_CURRENCIES.CURRENCY='EUR'

OR

SELECT * 
FROM CONTRACTS_IN_DIFFERENT_CURRENCIES WHERE
`CONTRACTS_IN_DIFFERENT_CURRENCIES`.`CURRENCY`="USD" OR `CONTRACTS_IN_DIFFERENT_CURRENCIES`.`CURRENCY`="EUR"

I have not understand ,1,0) that part. So if you need some more condition use AND, OR and combination of brackets to achieve result.

Just to let you know, if you need disctinc (non repeating values) you can use word DISTINCT and state what fields exactly you need.

Upvotes: 0

Related Questions