Menu
I been going thru some of the sql syntax to study for the oracle sql exam, I found something rather confusing
based on the official references, the select syntax is as follow :
SELECT
[ hint ]
[ { { DISTINCT | UNIQUE } | ALL } ]
select_list
FROM { table_reference | join_clause | ( join_clause ) }
[ , { table_reference | join_clause | (join_clause) } ] ...
[ where_clause ]
[ hierarchical_query_clause ]
[ group_by_clause ]
[ HAVING condition ]
[ model_clause ]
based on this you cannot have the HAVING clause before the GROUP BY clause . However if i were to execute the following sql in the test server :
select
department_id , count (*)
from
employees
having
count(*) > 6
group by
department_id ;
it does not produce a syntax error , can some one help explain this ? I don't like to think that the reference docs is wrong , but if so I need some confirmation.
629
You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.
You can use a SELECT command with a GROUP BY clause to group all rows that have identical values in a specified column or combination of columns, into a single row.
Having clause is only used with the SELECT clause. The expression in the syntax can only have constants. In the query, ORDER BY is to be placed after the HAVING clause, if any. HAVING Clause implements in column operation.
Yes, an SQL query can contain a WHERE and HAVING clause. You will use these together when you want to extract (or filter) rows for a group of data using a WHERE clause and apply a condition on the aggregate using the HAVING clause.
As stated here:
Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE. If you omit this clause, then the database returns summary rows for all groups.
Specify GROUP BY and HAVING after the where_clause and hierarchical_query_clause. If you specify both GROUP BY and HAVING, then they can appear in either order.
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
