Need Stored Procedure that adds only when matching column is true

Hello

I have 5 columns in a database with a value

val1
..
..
..
val5

and 5 matching bit columns

ValBit1
..
...
..
ValBit5


I need to sum up the values in val1 only when Valbit1 is true Val2 when Val2 is true etc etc

all returning 1 summed value

I need to do this is a stored procedure not sure how to proceed?

Thanks


Charles BaldoSoftware DeveloperAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Whether you do this in a stored procedure or otherwise, the solution is likely the same if I understand your requirement. Just use CASE.

SELECT 
   CASE ValBit1 WHEN 1 THEN Val1 ELSE 0 END +
   CASE ValBit2 WHEN 1 THEN Val2 ELSE 0 END +
   CASE ValBit3 WHEN 1 THEN Val3 ELSE 0 END +
   CASE ValBit4 WHEN 1 THEN Val4 ELSE 0 END +
   CASE ValBit5 WHEN 1 THEN Val5 ELSE 0 END AS ValSum
FROM your_table
;

Open in new window

0
 
sameer2010Commented:
I think, you need to SUM these up. So, you could use:
SELECT 
   SUM(CASE ValBit1 WHEN 1 THEN Val1 ELSE 0 END +
   CASE ValBit2 WHEN 1 THEN Val2 ELSE 0 END +
   CASE ValBit3 WHEN 1 THEN Val3 ELSE 0 END +
   CASE ValBit4 WHEN 1 THEN Val4 ELSE 0 END +
   CASE ValBit5 WHEN 1 THEN Val5 ELSE 0 END) AS ValSum
FROM your_table
;

Open in new window

0
 
Charles BaldoSoftware DeveloperAuthor Commented:
Thank You all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.