7 Replies Latest reply on May 21, 2006 9:41 PM by JohnGree

    sum 2 columns

    JohnGree Level 1
      Hi i am using this statement in my query

      SUM(Iif (BatRuns >= 100, 1, 0)) AS Hundred

      this works fine but i now need to add another column BatRuns2 to this i have tried

      SUM(Iif (BatRuns >= 100, 1, 0)(BatRuns2 >= 100, 1, 0)) AS Hundred

      just not sure how to do it
      can someone help
      thanks
        • 1. Re: sum 2 columns
          Dan Bracuk Level 5
          quote:

          Originally posted by: JohnGree
          Hi i am using this statement in my query

          SUM(Iif (BatRuns >= 100, 1, 0)) AS Hundred

          this works fine but i now need to add another column BatRuns2 to this i have tried

          SUM(Iif (BatRuns >= 100, 1, 0)(BatRuns2 >= 100, 1, 0)) AS Hundred

          just not sure how to do it
          can someone help
          thanks

          I don't know the answer, but, usually when you want to add two numbers together you put a plus sign between them. If that is not what you are attempting, your question is too vague.
          • 2. Re: sum 2 columns
            JohnGree Level 1
            ok sorry what i need to do is count how many time 100 or more appears in columns

            batruns
            and
            batruns2
            • 3. Re: sum 2 columns
              Dan Bracuk Level 5
              Does you db support this syntax?
              select case
              when this then that
              when these and those then the_other_one
              else something_else
              end as something

              If so, it would be a better approach.
              • 4. Re: sum 2 columns
                richy2424
                i am using microsoft access, not sure if this code would work
                • 5. Re: sum 2 columns
                  MikerRoo Level 1
                  For EITHER column having >= 100:
                  SUM (Iif ( (BatRuns >= 100) OR (BatRuns2 >= 100), 1, 0) ) AS Hundred

                  For BOTH columns having >=100 simultaneously:
                  SUM (Iif ( (BatRuns >= 100) AND (BatRuns2 >= 100), 1, 0) ) AS Hundred

                  Edit: fixed parenthesis
                  • 6. Re: sum 2 columns
                    MikerRoo Level 1
                    For SUM of columns >= 100:
                    SUM (Iif ( (BatRuns + BatRuns2 ) >= 100, 1, 0) ) AS Hundred



                    Edit: fixed parenthesis
                    • 7. Re: sum 2 columns
                      JohnGree Level 1
                      Thanks for that i have used
                      SUM (Iif ( (BatRuns >= 100) OR (BatRuns2 >= 100), 1, 0) ) AS Hundred

                      but it only ads the BatRuns2 column, not sure why this is

                      i just need to count the number of times 100 or more appears in these columns, the code seams logical to me but doesnt work

                      any ideas