SELECT query using multiple AND in where clause

Question

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 | -----------------------------------------------------------------

M Khalid Junaid · Accepted Answer · 2014-07-20 05:40:58Z

1

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`

answered Jul 20, 2014 at 5:40

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3855343 Over a year ago

Thanks for the response , i am getting syntax error at using HAVING COUNT(DISTINCT YEAR) =5 , if removed syntax is fine and also YEAR defined without colon only works

M Khalid Junaid Over a year ago

@user3855343 see demo please check your mysql version or how you are trying to run your query ? in fiddle it works as you can see demo

Collectives™ on Stack Overflow

SELECT query using multiple AND in where clause

1 Answer 1

`Demo`

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related