Search code examples
sqlif-statementrjdbc

Applying if logic in SQL query?

my Database table is in the format as shown below :

Current Year = 2017

ID     Bought    Year
1       A        2016
1       A        2015
2       A        2013
2       B        2015 
2       B        2014
3       A        2014
4       A        2014 
4       A        2015  
4       A        2016

List the customers who have been buying Product'A' last two years ( Bought in both T-1 and T-2 year )

Ans - ID : 1 and 4

How do I formulate a query in R , using 

lasttwoyearcustlist=DBgetQuery(conn, "Query")
Solution

  • a way is based on a In counting the number of distinc year 

    select id 
from my_table 
where  bought = 'A'
and year in ( 2016, 2015) 
group by id
having count(distinct year) =2