user2624242 user2624242 - 3 months ago 7
SQL Question

sql query search for vertical saving

enter image description here

I need to get all the booktypes which has salesyear only in 2013,2014 and should not have sales in any other year. So in my example data, should only return 'nonfiction'.

I have tried

select booktype where salesyear in (2013,2014)


this is returning both fiction and nonfiction, but I need to get the data if the salesyear has only in 2013, 2014 but not in any other year

I am using SQL Server 2008

Answer

You can use conditional aggregation:

SELECT booktype
FROM yourTable
GROUP BY booktype
HAVING SUM(CASE WHEN salesyear = 2013 THEN 1 ELSE 0 END) > 0 AND   -- 2013 present
       SUM(CASE WHEN salesyear = 2014 THEN 1 ELSE 0 END) > 0 AND   -- 2014 present
       SUM(CASE WHEN salesyear IN (2013, 2014) THEN 1 ELSE 0 END) = COUNT(*)   -- only 2013 and
                                                                               -- only 2014
Comments