Sum function with logic?

wallaceoc · #1 (**permalink**) 10-29-07, 07:31

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

Pat Phelan · #2 (**permalink**) 10-29-07, 07:42

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

wallaceoc · #3 (**permalink**) 10-29-07, 09:34

Yes the CASE statement did the trick.

Thanks for the quick reply.

Wallace

wallaceoc · #4 (**permalink**) 10-31-07, 06:56

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

r937 · #5 (**permalink**) 10-31-07, 08:08

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

wallaceoc · #6 (**permalink**) 10-31-07, 08:18

Thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech