Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    14

    Unanswered: Sum function with logic?

    Hi,

    I want to use the sum function to get the sum of a * b where a is always the value of the "face" column but b can be the value from one of 2 columns dependent on a certain condition. So simply my SQL would look like this if b was a fixed column:

    select sum(a*b)
    from tableA, tableB....

    What is the best way to add logic to this. Basically, below is what I want to do:

    select sum(a * [if tableA.code = 1 then use tableA.rate Else use tableB.rate])
    from tableA, tableB


    I hope I have explained this properly.

    Regards,
    Wallace

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does your SQL implementation support the CASE statement? If it does, that is your answer.

    Beware the cartesian join. Those can be sub-optimal.

    -PatP

  3. #3
    Join Date
    Oct 2007
    Posts
    14
    Yes the CASE statement did the trick.

    Thanks for the quick reply.

    Wallace

  4. #4
    Join Date
    Oct 2007
    Posts
    14
    I have one last question about the CASE statement and can't find the answer anywhere...

    Is it possible to specify multiple conditions in the WHEN part of the statement?

    Something like this:

    select.....
    CASE type
    WHEN 6 OR 7 OR 8 THEN Rate1
    ELSE
    Rate2
    ......
    END


    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, but using the other form of the CASE expression
    Code:
    CASE WHEN type IN ( 6 , 7 , 8 )
         THEN Rate1
         ELSE Rate2
     END
    the form that you were using would look like this
    Code:
    CASE type
      WHEN 6 THEN Rate1
      WHEN 7 THEN Rate1
      WHEN 8 THEN Rate1
             ELSE Rate2
     END
    and i prefer the former
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2007
    Posts
    14
    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •