What is the name of the category of functions used to summarize data?
Explanation
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.
Theory
  • The HAVING Clause

    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;
    
  • SQL HAVING Example

    
    |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.
    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;
    
    Read more: SQL HAVING Clause
  • SQL HAVING Example

    
    |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.
    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:
    • AVG() - Returns the average value
    • COUNT() - Returns the number of rows
    • FIRST() - Returns the first value
    • LAST() - Returns the last value
    • MAX() - Returns the largest value
    • MIN() - Returns the smallest value
    • SUM() - Returns the sum
    Read more: SQL Functions

Следи за CodeGalaxy

Мобильное приложение Beta

Get it on Google Play
Обратная Связь
Продолжайте изучать
тесты по SQL
Cosmo
Зарегистрируйся сейчас
или Подпишись на будущие тесты