# Thread: Sum function with logic?

1. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

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

-PatP

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

Wallace

4. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

6. Registered User
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
•