Data Science Explorer

[Basic Grammar] SQL HAVING Clause 본문

SQL

[Basic Grammar] SQL HAVING Clause

grace21110 2023. 10. 19. 18:06
반응형

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. 

 

  • HAVING Syntax 
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example 

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

 

Example

The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

 

Exercises

1. Write an SQL query to list the customers who have made more than 5 purchases. Use the "Customers" and "Orders" tables for this task.

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    GROUP BY CustomerID
    HAVING COUNT(OrderID) > 5
);

2. Write an SQL query to determine if the customers "Smith" or "Johnson" have placed more than 50 orders. Use the "Customers" and "Orders" tables for this task.

SELECT CustomerName, CASE
  WHEN CustomerName IN ('Smith', 'Johnson') AND CustomerID IN (
    SELECT CustomerID
    FROM Orders
    GROUP BY CustomerID
    HAVING COUNT(OrderID) > 50
  ) THEN 'Yes'
  ELSE 'No'
  END AS MoreThan50Orders
FROM Customers
WHERE CustomerName IN ('Smith', 'Johnson');

'SQL' 카테고리의 다른 글

[Basic Grammar] SQL ANY and ALL  (0) 2023.10.24
[Basic Grammar] SQL EXISTS  (0) 2023.10.23
[Basic Grammar] SQL GROUP BY Statement  (0) 2023.10.19
[Basic Grammar] SQL UNION Operator  (0) 2023.10.19
[Basic Grammar] SQL FULL OUTER JOIN  (0) 2023.10.18