SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
|OrderID |EmployeeID|ShipperID |
+--------+----------+-----------+
|10248 | 5 | 3 |
+--------+----------+-----------+
|10249 | 6 | 1 |
+--------+----------+-----------+
|10250 | 4 | 2 |
And a selection from the "Employees" table:
|EmployeeID |LastName |
+-----------+---------+
|1 |Davolio |
+-----------+---------+
|2 |Fuller |
+-----------+---------+
|3 |Leverling|
Now we want to find if any of the employees has registered more than 10 orders.
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
Read more: SQL HAVING Clause
|OrderID |EmployeeID|ShipperID |
+--------+----------+-----------+
|10248 | 5 | 3 |
+--------+----------+-----------+
|10249 | 6 | 1 |
+--------+----------+-----------+
|10250 | 4 | 2 |
And a selection from the "Employees" table:
|EmployeeID |LastName |
+-----------+---------+
|1 |Davolio |
+-----------+---------+
|2 |Fuller |
+-----------+---------+
|3 |Leverling|
Now we want to find if the employees "Davolio" or "Fuller" have registered more than 25 orders.
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
Login in to like
Login in to comment