HasS HasS - 5 months ago 11
MySQL Question

Strange (unknown) MySql Behaviour

Ok , consider I have a Database with one table call it Table A .
Table A Have Id int ,sum int.
Supose I want the sum values of table A that are greater than the Average of sum values.

For this I made two queries :

SELECT
a.id
FROM
TableA a,
(SELECT AVG(sum) AS mes FROM TableA) b
WHERE
a.sum>b.mes;


SELECT
a.id
FROM
TableA a
WHERE
a.sum > (SELECT AVG(a.sum))
GROUP BY
a.id;


The way I see it these queries are the same , but the result is Ok for the first and Null for the second.

My question is simple , is there a logic error in second query? If yes , can someone explain why?

Answer

yes those two queries are not equal. because in first query you are treating them as two individual tables and get the result set and finally compare it.

but in second query your performing aggregate function in that case you will get average for individual a.id

execute this you will understand what i mean

Select a.id from TableA a where a.sum = (Select avg(a.sum)) group by a.id;