1

it's correctly to use HAVING clause without mentioning the attribute in the the group by ? for example :

select ncl 
from commande c, ligne_commande lc 
where c.nc = lc.nc
group by ncl
having count(nart) = ...

nart attribute is not defined in group by

The Impaler
  • 45,731
  • 9
  • 39
  • 76
develop java
  • 29
  • 3
  • I work under oracle. From what I understood from the course, the group by clause will run first before the having clause; so group by go then extract the NCL column from the main table and group it, how then having is going to do some filtering on the values of a column NART that does not exist in the group? – develop java Dec 14 '21 at 17:49
  • 3
    You're not grouping by `nart` so naturally it should not be in the group by; what's more of an issue is using [30-year old join syntax](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – Stu Dec 14 '21 at 17:50
  • the attributes of the select clause must be specified in the group by clause and; I find that logical, on the other hand with the same principle, I think the group by clause will create a virtual table with a single column which is in our example NCL, after that, having will apply a filtering on the values ​​contained in the only column NCL , HAVING which follows a groupby will not refer to the main table in the database, but it will only work on the virtual table defined by the group by clause...I think this is the idea I have in mind are not correct :( – develop java Dec 14 '21 at 18:06
  • 1
    It is not filtering on the values of NART, it is filtering over the values of an aggregate function over that column. –  Dec 14 '21 at 18:15
  • [SQL - HAVING vs. WHERE](https://stackoverflow.com/q/9253244/3404097) – philipxy Jan 15 '22 at 04:57

1 Answers1

0

The terms in the having clause must be properties of the group - i.e., either columns you've grouped on, or aggregate expressions. count(nart) is an aggregate term (the number of non-null nart values in the group), and thus is perfectly legal in the having clause.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    if I understood correctly, when I use HAVING with an aggregation function, I can secify attributes which are not indicated in the group by clause, on the other hand if I do not use an aggregation function in HAVING, I must then specify the atrributes of the group by clause in having ... do I have well understood ? – develop java Dec 14 '21 at 18:24
  • @developjava yes, that is correct – Mureinik Dec 14 '21 at 18:53