CODEWITHSUNDEEP
mysqlcount
andreaem
andreaem

Reputation: 1655

MySQL SELECT where there are two clauses and the second can be three values

I want to count rows in a table where there is correspondence with username and status. The status can be 1,2 or 3.

   | ID | username | status |
   --------------------------
   | 1  | john     |   1    |
   | 2  | john     |   2    |
   | 3  | john     |   3    |
   | 4  | john     |   4    |
   | 5  | jenna    |   2    |
   --------------------------

There are 4 entries for user John, but the last has status 4 so I don't need to count it.

So I made this query

SELECT count(*) FROM orders WHERE username = 'john' AND status = 1 OR 2 OR 3

but this counts every row even if there are records with a username and status not in the query.

Upvotes: 0

Views: 45

Answers (3)

Sam Dufel
Sam Dufel

Reputation: 17598

When you write something like "1 OR 2 OR 3", mysql treats that as an expression - "1 OR 2 OR 3" just evaluates to TRUE.

You can either expand it - status = 1 OR status = 2 OR status = 3
Or you can use the IN operator: status IN (1, 2, 3)

Upvotes: 3

Dan Soap
Dan Soap

Reputation: 10248

You need to add the field to each comparison and also make sure the conditions are grouped correctly:

SELECT count(*) 
FROM orders 
WHERE username = 'john' 
  AND (status = 1 OR status = 2 OR status = 3)

Shorter syntax:

SELECT count(*) 
FROM orders 
WHERE username = 'john' 
  AND status in (1,2,3)

Upvotes: 4

Sumeet Gupta
Sumeet Gupta

Reputation: 198

Try this

 SELECT count(*) FROM orders WHERE username = 'john' AND status IN ( 1,2, 3)

Upvotes: 5

Related Questions