CODEWITHSUNDEEP
mysql
user3855343
user3855343

Reputation: 43

SELECT query using multiple AND in where clause

I am new to mysql and need help. I have a table as below and am trying to run a sql query similar to the one shown below. I have a problem in defining years in the format below.

My intended query is to find item codes where sales > 20, profit> 20, and tsv > 10 for all 5 five years. If even a single year doesn't have the comparison value, it shouldn't be listed.

select Item from inventory 
WHERE Sales > 20 AND Profit> 20 and TSV > 10 
AND YEAR ( 2010 AND 2011 AND 2013 AND 2009 AND 2012) 



+----------+-----------+--------+----------+-----------+--------+
| Item     |     YEAR  |  Sales |   Profit |  TSV      |   REC  |
+----------+-----------+--------+----------+-----------+--------+
| machine  |      2011 |      10|     10   |   20      |     10 |
| machine  |      2010 |      8 |     15   |    6      |      2 |
| machine  |      2013 |      15|     5    |   11      |      5 |
| machine  |      2009 |      3 |     8    |    9      |     13 |
| machine  |      2012 |      6 |     12   |   18      |     16 |
| decors   |      2010 |      11|     10   |   13      |     22 |
| decors   |      2011 |      9 |     9    |   16      |     12 |
| decors   |      2012 |      3 |     7    |   11      |      9 |
| decors   |      2013 |      13|     23   |    9      |     10 |
| decors   |      2009 |      4 |     9    |   10      |      8 |
| sp parts |      2009 |      22|     25   |   17      |     22 |
| sp parts |      2010 |      21|     22   |   11      |     14 |
| sp parts |      2011 |      28|     30   |   29      |     23 |
| sp parts |      2012 |      23|     28   |   12      |      8 |
| sp parts |      2013 |      21|     24   |   24      |     32 |
-----------------------------------------------------------------

Upvotes: 1

Views: 1224

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

This should do the trick,by using count with group by to check for each distinct year 

SELECT Item FROM 
inventory 
WHERE Sales > 20 AND Profit> 20 AND TSV > 10 
AND `YEAR` IN( 2010,2011,2013,2009,2012) 
GROUP BY Item
HAVING COUNT(DISTINCT `YEAR`) =5

Demo

Upvotes: 1

Related Questions