Search for a group dimension in SQL — HAVING clause

When you make a statistical calculation in SQL, you usually need to find something precise. Such as, for example, average sales by product category or the number of calories burned over different sports activities. In this situation, knowing how to use the HAVING clause is crucial. In this article, I will give an example of the query and tell you the difference between WHERE clauses.

HAVING Clause purpose — to find group logs

Let’s go directly to an example. We have a table with card game results, scores, dates, players, and identification numbers.

Card Games Results. GitHub Pamelafox Code

I used open-sourced code from GitHub. Let’s assume you want to find the sum of all games grouped by game name where the score will be more than 10.

Option 1. WHERE Clause — Wrong results

You can rely on your instincts and logically choose the WHERE Clause. So, we choose two columns — game_name and SUM(score). We specify a score >10 and group the results by game_name. Let’s look at what we get.

SELECT game_name, SUM(score) from card_games where score >10 group by game_name;

Total_score with WHERE clause

So, we have the total score for the game Rummy, but what about GoFish? The overall score for this game must be 7+5+4=16, and it’s more than 10. That means that the WHERE clause, in this case, will work incorrectly. It’s important to remember that the WHERE clause works only for a single log.

Option 2. HAVING Clause — right results

HAVING clause, on the contrary, looks for all rows in a table. The query will look like

SELECT game_name, SUM(score) from card_games group by game_name having SUM(score) >10 ;

Total_score with HAVING clause

The difference between these two clauses is not only in a group or a single affiliation but also in position in the clause. HAVING goes in the end, whereas WHERE goes before GROUP BY.

You can use all possible aggregation functions, such as SUM, AVG, MAX, MIN, etc., with this clause.

Thank you for reading!