• Status: Solved
• Priority: Medium
• Security: Public
• Views: 216

# SQL Select Case Sum

I have a table whose records contain at least 3 fields... Field1, Field2 and Field3.

I'd like to loop through the table and add up all the instances when the above 3 fields are positive values.

Example... if Field1 and Field2 contain a 5 and Field3 contains a 0 in all 7 records the total would be 14.

My current statement:

``````SELECT sum(
case

when Field1 > 0 then 1
when Field2 > 0 then 1
when Field3 > 0 then 1

end)
``````

My current statement returns 1 if any field is positive... thus the 7 records above would return 7 instead of 14.

Thank you for the help.
0
Sheldon Livingston
• 2
• 2
• 2
1 Solution

Commented:
Just a thought, but try this:

``````SELECT sum(
case

when Field1 > 0 then Field1
when Field2 > 0 then Field2
when Field3 > 0 then Field3

end)
``````
0

freelancerCommented:

SELECT
sum(case when Field1 > 0 then 1 else 0 end)
+  sum(case when Field2 > 0 then 1 else 0 end)
+  sum(case when Field3 > 0 then 1 else 0 end)
FROM ...

so where Field1 > 0 and Field2 is > 0 and Field3 = 0 that would be 2 per row, for 7 rows = 14
0

ConsultantAuthor Commented:
lsavidge... that summed up the values... thus, using the 7 records above, it returned 35.

BTW... the fields will contain a variety of values... not just 5.
0

ConsultantAuthor Commented:
Sweet!  Thank you PortletPaul... this worked.
0

freelancerCommented:
No problem. Cheers, Paul
0

Commented:
Ahh, nice. I read the question differently!
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.