Monday, July 21, 2008

COUNT and GROUP BY

When using "COUNT" and "GROUP BY" in mysql, make sure that the count condition to run after group by, otherwise the sql will fail.
Example:

SELECT count(node.uid) as num, node.uid,
node.created AS node_created
FROM node
WHERE (node.type in ('advisor_update')) AND (node.status <> 0)
AND num > 2
GROUP BY node.uid
ORDER BY node_created DESC;


SELECT count(node.uid) as num, node.uid,
node.created AS node_created
FROM node
WHERE (node.type in ('advisor_update')) AND (node.status <> 0)
GROUP BY node.uid
HAVING num > 2
ORDER BY node_created DESC;


The fist statement will fail, use the 2nd statement instead.