need hint for a query 2

[bonosa]

I have a table with 10 columns across. I want to pull up all the records that have at least three of these columns nonzero. Any three. So what should my query look like? What sql syntax should I be looking at?
Thanks,
sb

 

[TonyGroves]

How about:
[tt]
SELECT *
FROM tbl1
WHERE
(col1<>0)+(col2<>0)+(col3<>0)+(col4<>0)+(col5<>0)
+(col6<>0)+(col7<>0)+(col8<>0)+(col9<>0)+(col10<>0)
>=3
[/tt]

 

[r937]

nice idea, tony, but won't work if any of the column values is null

what this needs is

WHERE
case when col1 <> 0 then 1 else 0 end
+case when col2 <> 0 then 1 else 0 end
+case when col3 <> 0 then 1 else 0 end
+case when col4 <> 0 then 1 else 0 end
+case when col5 <> 0 then 1 else 0 end
+case when col6 <> 0 then 1 else 0 end
+case when col7 <> 0 then 1 else 0 end
+case when col8 <> 0 then 1 else 0 end
+case when col9 <> 0 then 1 else 0 end
+case when col10 <> 0 then 1 else 0 end
>=3


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TonyGroves]

If there's any chance of any of the values being null, then you could use instead:
[tt]
SELECT *
FROM tbl1
WHERE
if(col1<>0,1,0)+if(col2<>0,1,0)+if(col3<>0,1,0)
+if(col4<>0,1,0)+if(col5<>0,1,0)+if(col6<>0,1,0)
+if(col7<>0,1,0)+if(col8<>0,1,0)+if(col9<>0,1,0)
+if(col10<>0,1,0)
>=3
[/tt]

 

[r937]

the version with the IF functions is exactly the same as the version with the CASE expressions, except CASE is standard SQL and IF isn't



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ericbrunson]

Personally, I always try to stay away from non-standard SQL and I think it's a good idea to point out when an answer on this forum uses non-standard extensions, you'll find that I do in most of my posts.

 

[r937]

thanks for the support, eric



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[bonosa]

Thanks everyone for all the ideas. Happy holidays,
sb