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

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)

Open in new window


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
Asked:
Sheldon Livingston
  • 2
  • 2
  • 2
1 Solution
 
Lee SavidgeCommented:
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)

Open in new window

0
 
PortletPaulfreelancerCommented:
I think your description requires::

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
 
Sheldon LivingstonConsultantAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Sheldon LivingstonConsultantAuthor Commented:
Sweet!  Thank you PortletPaul... this worked.
0
 
PortletPaulfreelancerCommented:
No problem. Cheers, Paul
0
 
Lee SavidgeCommented:
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now