Geek Answers Handbook

SQL See If Two or More Columns in a Table Are Greater Than 0

I ran into a little problem and would appreciate any help.

My table is as follows:

CASH | CREDIT CARD | DEBIT CARD | ACCOUNT | OTHER ------------------------------------------------- 0.00 0.00 0.00 0.00 0.00 1.00 0.00 0.00 0.00 0.00 2.00 1.00 0.00 0.00 0.00

My goal is SELECT * FROM on any of the above rows containing more than one column> 0.

Thus, the third row will be selected in this scenario with the above table.

+5
source share
3 answers
 SELECT [CASH], [CREDIT CARD], [DEBIT CARD], [ACCOUNT], [OTHER] FROM table WHERE CASE WHEN [CASH] > 0 THEN 1 ELSE 0 END+ CASE WHEN [CREDIT CARD] > 0 THEN 1 ELSE 0 END+ CASE WHEN [DEBIT CARD] > 0 THEN 1 ELSE 0 END+ CASE WHEN [ACCOUNT] > 0 THEN 1 ELSE 0 END+ CASE WHEN [OTHER] > 0 THEN 1 ELSE 0 END >= 2
+10
source

I prefer t-clausen's answer , but as an exercise, I decided to try it as UNPIVOT followed by PIVOT so that we can write it using more common SQL tools:

 declare @t table (SomeID int,Cash money,Credit money,Debit money,Account money,Other money) insert into @t(SomeID,Cash,Credit,Debit,Account,Other) values (1,0.00,0.00,0.00,0.00,0.00), (2,1.00,0.00,0.00,0.00,0.00), (3,2.00,1.00,0.00,0.00,0.00) ;With Unpiv as ( select *,SUM(CASE WHEN MoneyValue > 0.00 THEN 1 ELSE 0 END) OVER (PARTITION BY SomeID) as cnt from @tt unpivot (MoneyValue for MoneyType in (Cash,Credit,Debit,Account,Other)) x ), Repiv as ( select * from Unpiv u pivot (SUM(MoneyValue) for MoneyType in (Cash,Credit,Debit,Account,Other)) x where cnt >= 2 ) select * from Repiv

This assumes that you have another column (called SomeID here), by which each row can be uniquely identified.

Result:

 SomeID cnt Cash Credit Debit Account Other ----------- ----------- --------------------- --------------------- --------------------- --------------------- --------------------- 3 2 2.00 1.00 0.00 0.00 0.00

Just hoping that the above can be more adapted to some of the requirements.

+1
source 
 SELECT [CASH], [CREDIT CARD], [DEBIT CARD], [ACCOUNT], [OTHER] FROM table WHERE ( SELECT COUNT(*) FROM (VALUES ([CASH]),([CREDIT CARD]),([DEBIT CARD]),([ACCOUNT]),([OTHER])) t(value) WHERE value > 0 ) >= 2
+1
source

More articles:


All Articles