filter a column to show only two values

All we need is an easy explanation of the problem, so here it is.

I have a baseball dataset and for the column teamid I would like to retrieve only 2 teams.

This is my code:

SELECT teamID, yearID, salary
FROM   Salaries 
WHERE  s.teamID LIKE '%HOU%' AND  '%TEX%'
GROUP BY yearID
ORDER BY yearID ASC;

My code only shows me the TEX results.

DATA: Salaries Table

Expected result

teamID yearID salary
HOU 2013 100
TEX 2015 300

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

There are two problems with your query. First, (teamID, salary) is likely not functionally dependent of yearID so you can’t group by yearID alone. In this case you probably don’t need a group by clause at all. Second, WHERE s.teamID LIKE ‘%HOU%’ AND ‘%TEX%’ is probably not evaluated the way you expect. I suspect you want:

WHERE  s.teamID LIKE '%HOU%' OR s.teamID LIKE '%TEX%'

Note that you need the LIKE predicate twice and a logical operator (OR) to combine the result.

SELECT teamID, yearID, salary
FROM   salaries s
WHERE  s.teamID LIKE '%HOU%' OR s.teamID LIKE '%TEX%'
ORDER BY yearID ASC;

teamID  yearID  salary  s.teamID LIKE '%HOU%'   s.teamID LIKE '%TEX%'
HOU 2013    100 1   0
TEX 2015    300 0   1

Fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply