MySQL Query Incorrect Count

Question

I have a phone call database that has a field 'direction', named appropriately after the direction the call is going (in OR out). My problem is, when I do a search for either 'in' or 'out' calls, the number is completely wrong.

Example:
For the month of May,
TOTAL calls (in + out) : 13622
IN calls : 12637
OUT calls : 985

SELECT `start`, `direction`, `ref_id` 
FROM  `call_list` 
WHERE  `did` = 'xxxx' 
AND  `start` >= '2013-05-01 00:00:00' 
AND  `start` < '2013-06-01 00:00:00'

The above is an accurate count based on dumping the above query to CSV and actually looking at the list. However, when I add in the 'direction' search, the numbers returned are completely off:

SELECT `start`, `direction`, `ref_id` 
FROM  `call_list` 
WHERE  `did` = 'xxxx' 
AND  `start` >= '2013-05-01 00:00:00' 
AND  `start` < '2013-06-01 00:00:00' 
AND `direction` = 'In'

...will result in a total count of 13461, and

SELECT `start`, `direction`, `ref_id` 
FROM  `call_list` 
WHERE  `did` = 'xxxx' 
AND  `start` >= '2013-05-01 00:00:00' 
AND  `start` < '2013-06-01 00:00:00' 
AND `direction` = 'Out'

..yields a total count of 11018.

Consequently, if I group:

SELECT `direction`, count(*)
FROM  `call_list` 
WHERE  `did` =  'xxxx'
AND  `start` >=  '2013-05-01 00:00:00'
AND  `start` <  '2013-06-01 00:00:00'
GROUP BY `direction`

...I get the correct count for each which is all well and good except sometimes I need to run queries on the 'In' or 'Out' count separately and so my numbers are off.

The 'direction' column is varchar(3) utf8_unicode_ci.

If anyone can shed light on why I am receiving bum count numbers, I'd really appreciate it.

Thanks in advance

Answer 1

A wild guess but one of the common causes of getting more or less rows than expected is using ORs in your WHERE conditionals without proper parentheses

E.g.

SELECT `start`, `direction`, `ref_id` 
  FROM  `call_list` 
WHERE  `did` = 'xxxx' OR `did` = 'yyyy'  -- << here is the problem !!!
  AND  `start` >= '2013-05-01 00:00:00' 
  AND  `start` < '2013-06-01 00:00:00' 
  AND `direction` = 'In'

that should be

SELECT `start`, `direction`, `ref_id` 
  FROM  `call_list` 
WHERE  (`did` = 'xxxx' OR `did` = 'yyyy') 
  AND  `start` >= '2013-05-01 00:00:00' 
  AND  `start` < '2013-06-01 00:00:00' 
  AND `direction` = 'In'

Here is SQLFiddle demo