Oracle HAVING

What does the HAVING condition do in a query? 

The WHAT condition is like WHERE but works on standard returned group records a BY GROUP. 

Having applies to group registers list of qualifications, while where applies to individual files. 

Only groups that meet the DO criteria will be returned. 

Having required the attitude of THE BY GROUP. 

Where and WHAT can be used in the same query at the same time.

Presentation at Oracle HAVING proviso 

The ANT proviso is an optional condition of the SELECT declaration. It is used to filter groups of lines returned standard groups the GROUP BY condition. This is why the HAVING provision is generally used with the GROUP BY condition.

The SQL HAVING linguistic structure 

The overall language structure is : 

In this proclamation, the HAVING statement shows up following the GROUP BY condition. 

On the off chance that you utilize the HAVING provision without the GROUP BY statement, the HAVING condition works like the WHERE proviso. 

Note that the HAVING condition channels gatherings of columns while the WHERE proviso channels lines. This is a fundamental distinction between the HAVING and WHERE provisions.

 SELECT column_Name

 FROM Table_Name

 

 WHERE condition

 GROUP BY column_Name 

 HAVING condition  

SQL GROUP BY Examples : 

 SELECT Name, count(prix)

 FROM Client

 GROUP BY Name

 HAVING count(prix)>2000;

SQL GROUP BY & HAVING  Examples : 

Example 01: 

SELECT

    order_id,

    SUM( PRICE * QTS ) as res

FROM

    order_items

GROUP BY

     order_id

HAVING

    SUM( PRICE * QTS ) > 10000

ORDER BY

    res DESC;

Example 02: 

SELECT

    order_id,

    COUNT( item_id ) item_count,

    SUM( unit_price * quantity ) total

FROM

    order_items

GROUP BY

    order_id

HAVING

    SUM( unit_price * quantity ) > 500000 AND

    COUNT( item_id ) BETWEEN 10 AND 12

ORDER BY

    total DESC,

    item_count DESC;

Example 03: 

SELECT

    order_id,

    COUNT( item_id ) item_count,

FROM

    order_items

GROUP BY

    order_id

HAVING

    COUNT( item_id ) BETWEEN 10 AND 12

ORDER BY

    item_count DESC;

Example 04: 

The accompanying SQL articulation records the quantity of clients in every nation, arranged high to low (Only incorporate nations with in excess of 5 clients):

SELECT COUNT(ID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(ID) > 5

ORDER BY COUNT(ID) DESC;

Example 05: 

The accompanying SQL proclamation records the representatives that have enlisted in excess of 10 requests:

SELECT Last_Name, COUNT(Orders.OrderID) AS Number

FROM Orders 

INNER JOIN Employe ON 

                Orders.IDEmp = Employe.IDEmp)

GROUP BY Last_Name

HAVING COUNT(OrderID) > 10;

Example 06: 

The accompanying SQL articulation records if the representatives "Nm1" or "Nm2" have enrolled in excess of 50requests:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders

INNER JOIN Employees ON 

Orders.EmployeeID = Employees.EmployeeID

WHERE LastName = 'Nm1' OR LastName = 'Nm2'

GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 50;