Choose correct clause to use in the following query:
SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode _____ COUNT(*) > 20;
Explanation
You would usually specify conditions in WHERE clause like following:
WHERE some_condition
But WHERE clause imposes conditions on columns present in the tables and cannot be used to impose restrictions on groups of records created by GROUP BY clause.
HAVING clause is used to places conditions on groups created by GROUP BY clause.

Some properties of HAVING clause:
- HAVING requires that a GROUP BY clause is present in the query
- WHERE and HAVING clauses can be both present in the same query
- HAVING clause applies to summarized group records, while WHERE clause applies to individual records
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
SQL Quiz
Start Quiz

or Read more about SQL Quiz

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback