By Bryan Young
Expert Author
Article Date: 2010-07-20
The other day, I was working on a fairly database-centric project, when I came across a problem I had never come across before. It seemed like such a simple task that I could not fathom why I could not write the query to get what I wanted. What I needed to do was to choose only the entries in a table which appeared a certain number of times.
My first instinct told me to do something like this.
SELECT COUNT(*), name FROM tableA GROUP BY name WHERE COUNT(*) > 10;
What do you mean "Invalid use of group function"? So I start investigating, and I find out that the count function cannot be used in the WHERE section of a query. This is because SQL will process the WHERE clause before the SELECT clause in order to know just what it is searching for.
The solution I found was to use the HAVING statement. Its syntax is similar to the WHERE clause, but it is executed after the SELECT clause, so it can be used to limit your results.
SELECT COUNT(*), name FROM tableA GROUP BY name HAVING COUNT(*) > 10;
I wanted to improve my query by showing the most popular entries first, and moving down the list. This I already knew how to do, but I wasn't sure if it would allow the count function. In this case, it does.
SELECT COUNT(*), name FROM tableA GROUP BY name HAVING COUNT(*) > 10 ORDER BY COUNT(*) DESC;
After years of writing SQL statements, I could hardly believe that there was a clause that I had never used, much less heard about. I had also never thought about the execution order of my queries. Overall, it was an eye-opening experience, which has increased my SQL know-how by leaps and bounds.
