How to use the having statement in oracle

In Oracle, the having statement is used to filter the row groups returned by the "GROUP BY" clause. It is an optional clause of the SELECT statement. This clause is usually used in conjunction with the "GROUP BY" clause. The syntax is "SELECT ... FROM ... GROUP BY ... HAVING ...;".

The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.

How to use the having statement in oracle

HAVING clause to filter the group returned by the GROUP BY clause.

Introduction to Oracle HAVING clause The HAVING clause is an optional clause of the SELECT statement. It is used to filter the grouping of rows returned by the GROUP BY clause. This is why the HAVING clause is usually used with the GROUP BY clause.

The following illustrates the syntax of the Oracle HAVING clause:

SELECT
    column_list
FROM
    T
GROUP BY
    c1
HAVING
    group_condition;
SQL

In this statement, the HAVING clause immediately follows the GROUP BY clause. If you use the HAVING clause without a GROUP BY clause, the HAVING clause will work like a WHERE clause.

Please note that the HAVING clause filters grouped rows, while the WHERE clause filters rows. This is the main difference between HAVING and WHERE clauses.

Example of Oracle HAVING clause

We will use the order_items table in the sample database for demonstration.

1. Simple Oracle HAVING example The following statement uses the GROUP BY clause to retrieve the value of the order and its total amount from the order_items table:

SELECT
    order_id,
    SUM( unit_price * quantity ) order_value
FROM
    order_items
GROUP BY
    order_id
ORDER BY
    order_value DESC;
SQL

Execute The above query statement yields the following results -

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to use the having statement in oracle. For more information, please follow other related articles on the PHP Chinese website!