Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | ||
6 | 7 | 8 | 9 | 10 | 11 | 12 |
13 | 14 | 15 | 16 | 17 | 18 | 19 |
20 | 21 | 22 | 23 | 24 | 25 | 26 |
27 | 28 | 29 | 30 | 31 |
Tags
- start exercise
- __init__
- error
- multiple lines
- train/test
- machine learning
- line color
- PROJECT
- break
- Python
- data distribution
- self parameter
- PANDAS
- pie charts
- matplotlib
- iterates
- MySQL
- Text mining
- SQL
- Default X points
- AS
- line width
- For loops
- polynomial regression
- Github
- continue
- Else
- matplotlib.pyplot
- Text Analytics
- variables
Archives
- Today
- Total
Data Science Explorer
[Basic Grammar] SQL HAVING Clause 본문
반응형
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 |