CODEWITHSUNDEEP
sqlsql-serverselect
Barry D.
Barry D.

Reputation: 547

SQL See Whether Two or More Columns In a Table is Greater Than 0

I have ran in to a little problem and would appreciate any help.

My Table is such:

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 aim is to SELECT * FROM any of the above rows that have more than one column > 0.

So the third row would be selected in this scenario with the above table.

Upvotes: 6

Views: 2151

Answers (3)

Anon
Anon

Reputation: 10908

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

I prefer t-clausen's answer but just as an exercise, I decide to try it as an UNPIVOT followed by a PIVOT, so that we could write it using more of the general 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 @t t
        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 does assume that you've got another column (here, called SomeID) 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 the above might be more adaptable for some variants of the requirements.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

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

Upvotes: 11

Related Questions