Combining AND and OR in SQL

Question

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

Possible duplicate of [SQL Logic Operator Precedence: And and Or](http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) — sstan, Jul 12 '16 at 16:18
Just to clarify to OP .. it's being interpreted as: `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` with the added paranthesis, you should be able to see why you got the results you did ;) Ghost's and Gordon's answers below are spot on. — Ditto, Jul 12 '16 at 16:22

score 4 · Accepted Answer · answered Jul 12 '16 at 16:15

4

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)

answered Jul 12 '16 at 16:15

UnhandledExcepSean

12,504
2
35
51

I prefer your second way . Thank you – Yuu Jul 12 '16 at 16:28

score 3 · Answer 2 · answered Jul 12 '16 at 16:17

Use IN instead of OR:

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

You seem uncomfortable with boolean logic. I would recommend that you always include parentheses when mixing AND and OR.

Combining AND and OR in SQL

2 Answers2

Linked

Related