How to select subset of data from multiple columns in SQL?

February 22, 2022

Assume I have a table such as following and I would like to select customers who purchased more than 1 item.

customer	item1	item2	item3	item4
1	10	NULL	NULL	NULL
2	5	11	NULL	NULL
3	7	NULL	9	NULL
4	NULL	NULL	2	NULL
5	7	13	9	NULL

My final output should be something like

customer	item1	item2	item3	item4
2	5	11	NULL	NULL
3	7	NULL	9	NULL
5	7	13	9	NULL

My code is

SELECT  
       customer
      ,item1
      ,item2
      ,item3
       item4
FROM StoreData
WHERE item1 IS NOT NULL OR 
      item2 IS NOT NULL OR
      item3 IS NOT NULL OR
      item4 IS NOT NULL

But this seems to be incorrect? Any suggestions? Many Thanks

>Solution :

Try something like this:

WHERE 
(
 (CASE WHEN item1 IS NOT NULL THEN 1 ELSE 0 END) 
+(CASE WHEN item2 IS NOT NULL THEN 1 ELSE 0 END) 
+(CASE WHEN item3 IS NOT NULL THEN 1 ELSE 0 END) 
+(CASE WHEN item4 IS NOT NULL THEN 1 ELSE 0 END) 
) > 1