joanb joanb - 6 months ago 9
SQL Question

Difference on sum totals on mysql query

I am having different results on what appears to be the same query. I am very confused on why the total differs from the queries. Hopefully someone has an explanation on this issue and maybe say which should be the correct total. Thank you

select sum(tm_hours)
FROM bhds_timecard
WHERE (tm_notes = 'Session'
OR tm_notes = 'Intake'
OR tm_notes = 'OT'
or tm_notes = 'PT'
or tm_notes = 'Speech')
AND (tm_date BETWEEN '2016-04-01' AND '2016-04-30')


This totals 1792.25

SELECT SUM(tm_hours)
FROM bhds_timecard
WHERE (tm_date BETWEEN '2016-04-01' AND '2016-04-30')
AND tm_notes = 'Session'
OR tm_notes = 'Intake'
OR tm_notes = 'OT'
OR tm_notes = 'PT'
OR tm_notes = 'Speech'


This totals 1796.25

Answer

The first query is evaluating all of your ORs as one condition -

WHERE (tm_notes = 'Session' 
 OR tm_notes = 'Intake' 
 OR tm_notes = 'OT' 
 or tm_notes = 'PT' 
 or tm_notes = 'Speech')
AND (tm_date BETWEEN '2016-04-01' AND '2016-04-30')

If a row meets any of those conditions, it evaluates as true and then moves on to the date constraint. So there's really only 2 top-level condition in the first query.

The second query actually has 6 conditions -

WHERE (tm_date BETWEEN '2016-04-01' AND '2016-04-30') 
AND tm_notes = 'Session' 
OR tm_notes = 'Intake' 
OR tm_notes = 'OT' 
OR tm_notes = 'PT' 
OR tm_notes = 'Speech'

If any of the 6 ORs are true, the whole WHERE clause is evaluated as true.

Comments