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;
0 Comments