FrostyPinky FrostyPinky - 4 months ago 7
SQL Question

Combining AND and OR in SQL

I'm having a hard time on getting the exact result in this query

SELECT isnull(SUM(a.Amount), 0) FROM tableName as a
WHERE a.ProgramID = 4 and a.AccountID = 475 and a.ActionCode = 1 OR a.ActionCode = 3


My Table

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
| AMOUNT | ProgramID | AccountID | ActionCode |
- - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 500 | 4 | 475 | 1 |
| 1000 | 4 | 475 | 1 |
| 1500 | 4 | 370 | 3 |
- - - - - - - - - - - - - - - - - - - - - - - - - - - -


Instead of getting total amount
1500
I get a result of
3000

Answer

Always use parens if you use OR.

SELECT isnull(SUM(a.Amount), 0)
FROM tableName as a 
WHERE a.ProgramID = 4
and a.AccountID = 475
and (
       a.ActionCode = 1
    OR a.ActionCode = 3
)

Or how I would write it:

SELECT isnull(SUM(a.Amount), 0)
FROM tableName as a 
WHERE a.ProgramID = 4
and a.AccountID = 475
and a.ActionCode IN (1,3)