SQL Aggregate 함수 = SUM, COUNT, AVG...   AVG, COUNT는 null은 세지 않음


COUNT
--------------------------------------------------------------------------
SELECT COUNT(*) FROM Products  
>>> 프로덕트의 행들, 데이터 레코드의 개수 세기, null 값 포함

SELECT COUNT(Price) FROM Products AS COUNT(Price) 
>>> 특정 컬럼의 행 갯수

SELECT COUNT(DISTINCT Price) FROM Products AS COUNT(Price)
>>> 중복값은 제외하고 셀 때  + null 값 미포함 


SUM, AVG(null 계산에서 제외처리됨 )
--------------------------------------------------------------------------
SELECT SUM(Price) FROM Products

SELECT AVG(Price) FROM Products



평균 구할 때, null 값을 0으로 처리해주고 싶을 때
--------------------------------------------------------------------------
SELECT SUM(Price)/COUNT(*) FROM Products
>>> 전체 로우 수로 나누기


최대값, 최소값
--------------------------------------------------------------------------
SELECT MAX(Price) FROM Products

SELECT MIN(Price) FROM Products


SELECT COUNT(Price), MAX(Price), MIN(Price) FROM Products
--------------------------------------------------------------------------


공급자 기준, 평균가격 
--------------------------------------------------------------------------
SELECT SupplierID, AVG(Price) FROM Products
GROUP BY SupplierID


+ 기준 1개 추가
--------------------------------------------------------------------------
SELECT SupplierID, CategoryID, AVG(Price) AS AVG_Price FROM Products
GROUP BY SupplierID, CategoryID


더 나은 코드 스타일 + 코드 가독성
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID


오름차순 기준 정렬, Sorting
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID
ORDER BY AVG(Price)


내림차순 기준 정렬
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID
ORDER BY AVG(Price) DESC


집계 함수가 SELECT 문에 들어가도 상관없음
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products


WHERE 은 GROUP BY 이전에
HAVING 은 이후에


--------------------------------------------------------------------------
100불 이상 평균 가격 내림차순 정렬

SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID
HAVING AVG_Price >= 100
ORDER BY AVG(Price) DESC


Alias 활용
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS avg_price
FROM Products
GROUP BY SupplierID, CategoryID
HAVING avg_price >= 100


소수점 처리
--------------------------------------------------------------------------
CEIL() >>> 올림

FLOOR() >>> 내림

ROUND() >>> 반올림



CASE
--------------------------------------------------------------------------
SELECT CASE
WHEN categoryid = 1 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타'
END AS 'category name', *

FROM Products



AND 조건 1개 추가
--------------------------------------------------------------------------

SELECT CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타'
END AS 'category name', *

FROM Products


CASE + GROUP BY
--------------------------------------------------------------------------
SELECT CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '소스'
ELSE '기타'
   END AS 'NEW_CATEGORY', *
       
FROM Products
GROUP BY NEW_CATEGORY


--------------------------------------------------------------------------


SELECT 
CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '소스'
ELSE '기타'
END AS 'NEW_CATEGORY', AVG(Price)        >>> 반올림 포함하는 
       
FROM Products
GROUP BY NEW_CATEGORY






WHEN절의 순서 중요,
정삼각형 데이터 분리된 후 다음 케이스 분리하는 방식
--------------------------------------------------------------------------
SELECT
      CASE 
           WHEN A=B AND B=C THEN 'Equilateral' 
      
           WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'

           WHEN A=B OR A=C OR B=C THEN 'Isosceles'
           
           ELSE 'Scalene'
           
      END
      
FROM TRIANGLES



행렬 역변환, 데이터 피벗팅
--------------------------------------------------------------------------

SELECT 
CASE 
WHEN CategoryID = 1 THEN price
ELSE NULL
       END AS Category1_price, *       
      

FROM Products





SELECT 
      CASE 
    WHEN CategoryID = 1 THEN price
    ELSE NULL
      END AS Category1_price,
      
      CASE 
       WHEN CategoryID = 2 THEN price
    ELSE NULL
      END AS Category2_price,
      
      CASE 
    WHEN CategoryID = 3 THEN price
    ELSE NULL
      END AS Category3_price

FROM Products





>>> CASE문에 다른 함수 추가할 때, CASE부터 END까지 괄호 씌워야 


SELECT 
      AVG(CASE 
 WHEN CategoryID = 1 THEN price
        ELSE NULL
          END) AS Category1_price,
      
      AVG(CASE 
WHEN CategoryID = 2 THEN price
ELSE NULL
          END) AS Category2_price

FROM Products



Category1_price Category2_price
37.979166666666664 23.0625


차이 확인하기

SELECT CategoryID, AVG(Price)
FROM Products
GROUP BY CategoryID


CategoryID     AVG(Price)
1              39.2
2              40.3



-------------------------------------------------------------------------




INNER 조인 - 교집합
--------------------------------------------------------------------------

SELECT *
FROM Users

     INNER JOIN Orders ON Users.ID = Orders.userID


JOIN Orders  >>> 붙일 테이블

ON Users.ID = Orders.userID
>>> Users의 ID컬럼과 Orders의 userID 컬럼이 같을때, 
이것을 기준으로 해서 조인, 조인이 되는 기준 컬럼




SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID



SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Shippers ON Orders.ShippersID = Shippers.ShippersID


이너 중첩
--------------------------------------------------------------------------

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID


LEFT JOIN - Null 값이 포함될 수 있음.
--------------------------------------------------------------------------
SELECT *
FROM Users >>> 왼쪽에 두고 싶은 테이블
     LEFT JOIN Orders ON Users.Id = Orders.UserId    >>> User.Id 기준으로
              >>> 오른쪽에 두고 싶은 테이블


LEFT JOIN - 한번도 주문하지 않은 고객들
--------------------------------------------------------------------------
SELECT * FROM Customers
  LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

WHERE OrderID is null

+ Recent posts

티스토리툴바