I do not understand why the error?

Invalid use of group function

I know that swears on WHERE COUNT(users.cities_id) > 3 but do not know why?

 SELECT cities.name, COUNT(users.cities_id) FROM cities LEFT JOIN users ON users.cities_id = cities.id WHERE COUNT(users.cities_id) > 3 GROUP BY cities.name
  • (without reading too much) WHERE to HAVING changed? - AK ♦
  • changed, it turned out another error You have an error in your SQL syntax; I ’m line 4 - DivMan
  • added HAVING COUNT (users.cities_id)> 3 after grouping and it worked, and why is that? - DivMan
  • Since you do not show how to rewrite the question - then I have to ask "do you remember that HAVING should go after GROUP BY" or stupidly replaced one word with another? - AK ♦
  • Why did it work? Mmm, well, it will take longer to explain (especially since it depends a lot on your current level of understanding of relational databases), and it’s time for me to run away. If in the evening no one writes an answer - I will try not to forget about this question and write. - AK ♦

1 answer 1

Conditions with the results of group functions can be specified only in the HAVING part:

 SELECT cities.name, COUNT(users.cities_id) FROM cities LEFT JOIN users ON users.cities_id = cities.id GROUP BY cities.name HAVING COUNT(users.cities_id) > 3

To understand the logic of the SQL query, it is customary to represent the order in which its parts are executed as follows (in reality, for the purposes of optimization, the DBMS is done a little differently):

  1. from
  2. ON in join
  3. where
  4. group by
  5. having
  6. select
  7. order by
  8. offset, limit

All aggregate functions are calculated during phase 4, when a group by of primary lines collects total. During the execution of the where phase, which is designed to work with the primary rows of tables, groups, and the results of the calculation of group functions are not yet, therefore aggregate functions cannot be used in this phrase. The phrase having is used to filter the final lines generated during the grouping.

PS In principle, if having not been (and in some other cases) the same effect can be obtained as follows:

 SELECT * FROM ( SELECT cities.name, COUNT(users.cities_id) as CNT FROM cities LEFT JOIN users ON users.cities_id = cities.id GROUP BY cities.name ) X WHERE CNT > 3