Tuesday, 1 October 2013

MySQL - Why would nearly 2 identical queries result in 2 different results? GROUP BY

MySQL - Why would nearly 2 identical queries result in 2 different
results? GROUP BY

I have 2 queries that are nearly identical, one with a GROUP BY, one
without. The results are very different. The GROUP BY query results in
over double the non-GROUP BY query result.
Query 1:
SELECT table2.name, COUNT(DISTINCT(op.id))
FROM op INNER JOIN table1 ON table1.EID = op.ID
INNER JOIN table3 ON table3.id = table1.jobid
INNER JOIN table2 ON table2.id = table3.CatID
WHERE op.BID = 1
AND op.ActiveStartDate <= NOW()
AND op.ActiveEndDate >= NOW()
GROUP BY table2.name
ORDER BY COUNT(*) DESC;
Query 2:
SELECT op.Type, COUNT(DISTINCT op.id)
FROM op
WHERE op.BID = 1
AND op.ActiveStartDate <= NOW()
AND op.ActiveEndDate >= NOW()
ORDER BY op.Type ASC;
These should result in the same result. When playing around with them,
once I remove the "GROUP BY" from query 1, the result is the same. If I
put the "GROUP BY" back into Query 1, the result is more than doubled.

No comments:

Post a Comment