CODEWITHSUNDEEP
sqldatabase
Yuu
Yuu

Reputation: 619

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

Upvotes: 0

Views: 311

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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.

Upvotes: 3

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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)

Upvotes: 4

Related Questions