Aggregate functions are used to summarize the results of a particular column of selected data.
Some examples of aggregate functions: AVG, MIN, MAX, SUM, COUNT, etc.
Aggregate functions can appear in SELECT, ORDER BY and HAVING clauses.
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL HAVING Syntax
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;
Now we want to find if any of the employees has registered more than 10 orders.
We use the following SQL statement:
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;
Now we want to find if the employees "Davolio" or "Fuller" have registered more than 25 orders.
We add an ordinary WHERE clause to the SQL statement:
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;
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
Войдите чтобы поставить Нравится
Войдите чтобы прокомментировать