Using aggregate function on subquery

A developer was trying to compute the average of the first 100 records with LIMIT:

SELECT
  avg(con_hits) as avg_hits
FROM
  content
WHERE
  con_type = 1
  AND con_posttime < $twelve_hrs_ago
  AND con_refresh = 0
ORDER BY
  con_posttime DESC
LIMIT 100

Can you spot the subtle bug in the above query?

LIMIT is applied to the resultset, after AVG is calculated. The correct way is to use a subselect and apply the aggregate function:

SELECT avg(con_hits) as avg_hits
FROM (
  SELECT con_hits
  FROM content
  WHERE
    con_type = 1
    AND con_posttime < $twelve_hrs_ago
    AND con_refresh = 0
  ORDER BY con_posttime DESC
  LIMIT 100
) x;

Tagged as: sql