CODEWITHSUNDEEP
sql
user3471881
user3471881

Reputation: 2724

select columns based on multiple values

I have data which looks like:

data.csv
company  year  value
A        yz    x

I wan't to grab all columns when value is < x and year is 2005, 2006, 2007, 2008 etc.

SELECT * FROM data WHERE value < "X" AND year ="2005" AND year="2006" AND year="2007" AND year="2008";

Above results in nothing. So essentially: give me all companies for which value has been below X the last YZ years.

Upvotes: 0

Views: 71

Answers (3)

Patrick Hofman
Patrick Hofman

Reputation: 157116

You have a condition that can never be true. It can't be 2005 and 2006 at the same time.

Try in:

SELECT *
FROM   data
WHERE  value < "X"
AND    year in ("2005", "2006", "2007", "2008")
;

The in checks whether year is one of the values following.

Or or:

SELECT *
FROM   data
WHERE  value < "X"
AND    ( year = "2005"
         or year = "2006"
         or year = "2007"
         or year = "2008"
       )
;

The or just checks whether the left side or the right side condition is true.

Upvotes: 2

Michael Schneider
Michael Schneider

Reputation: 506

Consider to change a string format from a year to a datetime, then you can simply select like this:

SELECT * FROM data WHERE value < "X" AND year(year) >=2005;

else you can use:

SELECT * FROM data WHERE value < "X" AND YEAR IN (2005, 2006, 2007, 2008);

Upvotes: 0

SverreN
SverreN

Reputation: 177

This should work for you:

SELECT * FROM data WHERE value < "X" AND YEAR IN (2005, 2006, 2007, 2008)

Upvotes: 0

Related Questions