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:
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
BTW... the fields will contain a variety of values... not just 5.
ASKER
Sweet! Thank you PortletPaul... this worked.
No problem. Cheers, Paul
Ahh, nice. I read the question differently!
Open in new window