DB08 - 30일(sql28~34) - count, min, max, avg, sum, group by, having

28join.sql

♠ 테이블 하나 더 추가해서 3개(혹은 그 이상도 가능) join 해보기

SELECT 컬럼 FROM 테이블1 JOIN 테이블 2 ON 테이블1.컬럼 = 테이블2.컬럼 
JOIN 테이블3 ON 테이블2.컬럼 = 테이블3.컬럼;

예)

SELECT c.CustomerName, o.OrderDate, o.EmployeeID, e.LastName, e.FirstName
FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Employees e ON o.EmployeeID = e.EmployeeID
ORDER BY 1;

예)

-- 어떤 고객이 어떤 배송자(Shipper)를 사용했는 지 조회
-- 컬럼:고객명, 주문날짜, 배송자명, 배송자 전화번호
-- 테이블:Customers, Orders, Shippers
SELECT c.CustomerName, o.OrderDate, s.ShipperName, s.Phone 
FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Shippers s ON s.ShipperID = o.ShipperID
ORDER BY 1;

-- 주문 상세 내용도 추가 조회
-- 테이블 Customers, Orders, Shippers, OrderDetails, Products, Categories
-- 테이블 3개 이상 ~ 도 가능
SELECT c.ContactName, o.OrderDate, p.ProductName, ct.CategoryName, od.Quantity, s.ShipperName, s.Phone
FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Shippers s ON o.ShipperID = s.ShipperID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
JOIN Categories ct ON p.CategoryID = ct.CategoryID
ORDER BY 1, 2;

---

29functions-count.sql

♠ COUNT : row 수 (중복은 count, null 은 count 하지않음)

SELECT COUNT(컬럼명) FROM 테이블명;
SELECT COUNT(*) FROM 테이블명; -- 전체 컬럼

SELECT COUNT(*) FROM Customers;

SELECT COUNT(CsutomerID) FROM Customers;

 

> 중복을 count 하지 않게 하기 위해 DISTINCT 사용

SELECT COUNT(Country) FROM Customers;
SELECT COUNT(DISTINCT Country) FROM Customers; -- Customers 테이블에 Country 수 (중복없이)

 

예)

-- 연습1: Suppliers 의 city 컬럼의 값이 중복없이 몇 개 있는지?
SELECT count(DISTINCT city) FROM Suppliers;
-- 연습2: Suppliers 의 country 컬럼의 값이 중복없이 몇 개 있는지?
SELECT count(DISTINCT country) FROM Suppliers;

-- 연습3 : Seafood가 몇 번 주문되었는 지 조회
SELECT count(*) FROM OrderDetails od 
JOIN Products p ON od.ProductID = p.ProductID
JOIN Categories ct ON p.CategoryID = ct.CategoryID
WHERE ct.CategoryName = 'Seafood';

-- 연습4 : Seafood를 한 번이라도 주문한 적이 있는 고객 수 조회
SELECT c.CustomerID, c.CustomerName, ct.CategoryName FROM Orders o 
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON p.ProductID = od.ProductID
JOIN Categories ct ON p.CategoryID = ct.CategoryID
WHERE ct.CategoryName = 'Seafood'
ORDER BY 1;

SELECT count(DISTINCT c.CustomerID) FROM Orders o 
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON p.ProductID = od.ProductID
JOIN Categories ct ON p.CategoryID = ct.CategoryID
WHERE ct.CategoryName = 'Seafood';

연습4

연습4: Seafood 는 Categories 테이블의 CategoryName 컬럼, 고객은 Customers 테이블의 CustomerID 컬럼

Customers -  CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country

Orders - OrderID, CustomerID, OrderDate, ShipperID

OrderDetails - OrderDetailID, OrderID, ProductID, Quantity

Products - ProductID, ProductName, SupplierID, CategoryID, Unit, Price

Categories - CategoryID, CategoryName, Description

---

30functions-min-max.sql

♠ MIN, MAX : 최솟값, 최댓값

SELECT MIN(컬럼) FROM 테이블명;
SELECT MAX(컬럼) FROM 테이블명;

↓ 다음과 같이 정렬 하여 최대/최소를 조회할 수도 있지만

SELECT * FROM Products ORDER BY Price;
SELECT * FROM Products ORDER BY Price DESC;

↓ 다음과 같이 바로 출력할 수도 있음

SELECT MIN(Price) FROM Products;
SELECT MAX(Price) FROM Products;

 

예)

SELECT MIN(BirthDate) AS 가장이른생일 FROM Employees;
SELECT MAX(BirthDate) 가장늦은생일 FROM Employees;

 

♠ 쿼리 안의 쿼리 (nested query, subquery)

만약, 위의 예)에서 해당 값의 추가적인 정보를 조회하고 싶을 때,

SELECT * FROM 테이블명 WHERE 컬럼명 = (subquery);

SELECT * FROM Employees
WHERE BirthDate = (SELECT MIN(BirthDate) FROM Employees);

주의) = 연산자 사용 시 subquery 결과는 단일 (1 row)

예)

-- 연습1: 가장 나중에 태어난 사람(들) 조회
SELECT * FROM Employees 
WHERE BirthDate = (SELECT MAX(BirthDate) FROM Employees);

-- 연습2: 가장 저렴한 가격의 상품(들) 조회
SELECT * FROM Products WHERE Price = (SELECT MIN(Price) FROM Products);
-- 연습3: 가장 비싼 가격의 상품(들) 조회
SELECT * FROM Products WHERE Price = (SELECT MAX(Price) FROM Products);

---

31functions-avg-sum.sql

♠ AVG : 평균값 (null 은 계산에 포함하지 않음)

SELECT AVG(컬럼) FROM 테이블명; 

SELECT AVG(Price) FROM Products;

♠ SUM : 합

SELECT SUM(컬럼) FROM 테이블명;

SELECT SUM(Price) FROM Products;

 

예)

-- 연습1: SubQuery 사용해서 평균값 이하인 상품 목록
SELECT * FROM Products 
WHERE Price <= (SELECT AVG(Price) FROM Products); -- <= 서브쿼리는 단일(1row)

-- 연습2: SubQuery 사용해서 평균값 이상인 상품 목록
SELECT * FROM Products 
WHERE Price >= (SELECT AVG(Price) FROM Products);

-- 연습3: 1996-07-08 에 주문한 상품의 총 수량 (Quantity)
-- 사용테이블 : Orders, OrderDetails
SELECT SUM(od.Quantity) 
FROM Orders o JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE o.OrderDate = '1996-07-08';

---

32group-by.sql

♠ GROUP BY

위의 함수들 (count, min, max, avg, sum) 을 테이블 전체가 아닌 일부에서 사용하고 싶을 때,

SELECT 컬럼1, COUNT(컬럼2) FROM 테이블명
GROUP BY 컬럼1;

SELECT COUNT(CustomerID) FROM Customers; -- 테이블 전체에서 CustomerID count

SELECT Country, COUNT(CustomerID) FROM Customers
GROUP BY Country; -- 각 Country 안에서 CustomerID count

 

참고) ORDER BY

SELECT Country, count(CustomerID) FROM Customers
GROUP BY Country
ORDER BY 1; -- Country 이름 오름차순

SELECT Country, count(CustomerID) FROM Customers
GROUP BY Country
ORDER BY 2; -- count 오름차순 (적은 순서대로)

SELECT Country, count(CustomerID) FROM Customers
GROUP BY Country
ORDER BY 2 DESC; -- count 내림차순 (많은 순서대로)

 

예)

-- 연습1 : 나라별 Supplier 수 조회
SELECT Country, count(SupplierID) FROM Suppliers
GROUP BY Country
ORDER BY 2;

-- 연습2 : 가장 많은 고객이 있는 나라(들) 조회
SELECT 
    *
FROM
    (SELECT 
        Country, COUNT(CustomerID) Count
    FROM
        Customers
    GROUP BY Country) B
WHERE
    count = (SELECT 
            MAX(Count)
        FROM
            (SELECT 
                Country, COUNT(CustomerID) Count
            FROM
                Customers
            GROUP BY Country) A)
;

-- 연습3 : 가장 적은 고객이 있는 나라(들) 조회
SELECT 
    *
FROM
    (SELECT 
        Country, COUNT(CustomerID) Count
    FROM
        Customers
    GROUP BY Country) B
WHERE
    count = (SELECT 
            MIN(Count)
        FROM
            (SELECT 
                Country, COUNT(CustomerID) Count
            FROM
                Customers
            GROUP BY Country) A)
;

-- 연습4 : 고객별 주문 수 조회 (CustomerID, 주문수)
-- table: Orders
SELECT CustomerID, Count(OrderID) '주문수'
FROM Orders
GROUP BY CustomerID; -- CustomerID 별로!

-- 고객이름도 같이 조회
-- table: Customers, Orders
SELECT o.CustomerID, c.CustomerName, Count(o.OrderID) '주문수'
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID; -- CustomerID 별로!

-- 연습 5 : 고객별 총 주문한 수량 (CustomerID, 총 주문 수량)
-- tables : Orders, OrderDetails
SELECT o.CustomerID, c.CustomerName, sum(Quantity) '총 주문 수량'
FROM Orders o JOIN OrderDetails od ON o.OrderID = od.OrderID
              JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID; -- CustomerID 별로!

---

33having.sql

♠ HAVING : GROUP BY 를 사용할 때 WHERE 대신 쓰임 

SELECT 컬럼1, count(컬럼2) FROM 테이블명
GROUP BY 컬럼1
HAVING 조건;

SELECT Country, count(CustomerID) FROM Customers
GROUP BY Country
HAVING count(CustomerID) >= 10;

예)

-- 연습1 : 주문을 한 번만 한 사람들 조회 (CustomerName)
-- table : Orders, Customers
SELECT o.CustomerID, c.CustomerName, count(OrderID) 
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID
HAVING count(OrderID) = 1
;

-- 연습2 : 주문을 한번만 또는 한번도안한(0) 고객들 조회 (CustomerID, CustomerName, 주문수)
SELECT c.CustomerID, c.CustomerName, count(o.OrderID) count
FROM Orders o RIGHT JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID
HAVING count(o.OrderID) <= 1
ORDER BY count, c.CustomerId
;

-- 연습3 : 주문을 한번 또는 0번 받은 직원들 조회
SELECT e.EmployeeID, e.LastName, e.FirstName, count(o.OrderID)
FROM Employees e LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID
GROUP BY e.EmployeeID
HAVING count(o.OrderID) <= 1
;

 

★ GROUP BY + 여러개의 컬럼

여러개의 컬럼을 기준으로 나눌 수 있다

-- City 안에서 CustomerID 수를 출력
SELECT Country, City, COUNT(CustomerID)
FROM Customers
GROUP BY City
ORDER BY 1;

-- Country, City 안에서 CustomerID 수를 출력
SELECT Country, City, COUNT(CustomerID)
FROM Customers
GROUP BY Country, City
ORDER BY 1;

같아 보일수도 있지만, 위는 오직 City 만 기준으로 하였고 아래는 Country 까지 고려하였다

 

Customers 테이블에 Country 는 다르지만 City 는 같은 레코드를 넣어보자

(기존 테이블에 Belgium, Charleroi 가 있었는데 이번에는 Austria, Charleroi  를 추가했음)

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (92, 'Jennie', 'Jinnie', 'everywhere', 'Charleroi', '22222', 'Austria');

 

위의 코드대로 하면 다음과 같이 Country 도 구분하지만

아래 코드대로 하면 다음과 같이 Austria 의 Charleroi 도 같이 count 된다 (오직 City 기준이기 때문에)

---

34exercises.sql

연습

-- 상품별 판매수량
SELECT p.ProductID, p.ProductName, sum(od.Quantity) 
FROM OrderDetails od JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductID
;
-- 상품별 매출액
SELECT p.ProductID, p.ProductName, sum(od.Quantity) * p.Price '합'
FROM OrderDetails od JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductID
;
-- 날짜별 매출액
SELECT o.OrderDate, od.ProductID,
       p.ProductName, p.Price, od.Quantity,
       sum(p.Price * od.Quantity) '합'
FROM Orders o JOIN OrderDetails od ON o.OrderID = od.OrderID
			  JOIN Products p ON od.ProductID = p.ProductID
GROUP BY o.OrderDate
ORDER BY o.OrderDate
;