1

I was studying about the having clause when I came across this website.

The last two lines of this article have made me puzzled.

  1. Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause.
  2. Similar to the WHERE clause, it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions.

In the first sentence, what I understand is that the column names which appear in Group by clause must also appear in the Having clause.

For instance:
Group by empno.
Having empno. < 10;

But at many places the Having clause has a different column name than the Group By clause. So is it a mistake?

1
  • Be cautious about any web site that does not explicitly mention MySQL (or MariaDB). There can be a lot of differences between vendors.
    – Rick James
    Nov 10, 2021 at 23:51

2 Answers 2

1

It is a mistake to use them 'as is' in the HAVING clause, but it is possible to use other column names like this:

SELECT empno
  FROM orders
  GROUP BY empno
  HAVING SUM(value) > 1000

which will give you the employees who have a total order value of 1000 or more. In this case, value does not appear in the GROUP BY but it's an argument of SUM. Just like you can't do

SELECT empno, value
  FROM orders
  GROUP BY empno

(because value is not in the GROUP BY) but you can do

SELECT empno, SUM(value)
  FROM orders
  GROUP BY empno

A special case of this is using *, for example to get the employees with at least 10 orders, you can use this:

SELECT empno
  FROM orders
  GROUP BY empno
  HAVING COUNT(*) >= 10
5
  • column names that appear in the clause Must Also Appear as column names in the GROUP BY clause. Doesn't must also appear mean that if I am using empno. in Having clause it also has to appear in Group By clause. Nov 10, 2021 at 14:09
  • @Glorfindel...I understand that Select and Groupby should have same column names. But Having and Group by should also have same is making me confused. Nov 10, 2021 at 14:12
  • If you're using it without an aggregate function like SUM or COUNT, it must appear in the GROUP BY.
    – Glorfindel
    Nov 10, 2021 at 14:23
  • Would it be a right example then : SELECT empno FROM orders GROUP BY empno HAVING empno < 100. Because I am not using an aggregate function so empno in both the clauses. Nov 10, 2021 at 14:34
  • I have no idea, but in that case I would use SELECT empno FROM orders WHERE empno < 100 GROUP BY empno (or use a SELECT DISTINCT). HAVING is mostly useful for filtering based on aggregate functions.
    – Glorfindel
    Nov 10, 2021 at 15:10
1

It's not a mistake, but you need to pay specific attention to the first few lines of the linked article.

Group functions cannot be used in the WHERE clause. SQL statement can have both a WHERE clause and an HAVING clause. WHERE filters data before grouping and HAVING filters the data after grouping.

WHERE clause allows you to filter results before they get to the grouping statement, which will allow the query to perform faster as it means less data to aggregate overall.

HAVING clause will allow you to filter data after the grouping statement, and let you restrict output based on aggregate values.

I usually only use the HAVING clause if I want to filter results by the aggregated column (say I only want results where the total sales is higher than X). I use the WHERE clause to get only results for the year I want.

1
  • It's a mistake is to preface each of the two statements with "Similar to the WHERE clause...". Saying "the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause" is correct, but that's not at all similar to the WHERE clause, which does not require that the column references used in it be GROUP BY columns only. Same with "it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions" – that's a correct statement on its own, but "similar to the WHERE clause"? Surely not.
    – Andriy M
    Nov 10, 2021 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.