SQL문 실습 - CASE문(2)

SQL문 실습 - CASE문(2)

이전 글에 이어서 CASE문에 대해서 예제를 풀어보면서 알아보겠습니다.

SQL문 실습 - CASE문(1)

SQL문 실습 - CASE문(3)

6. 

--  Use Orders table
--    Select OrderDate in 1996
--    Select EmployeeID and First_Quarter, Second_Quarter, Third_Quarter, Fourth_Quarter
--    First_Quarter -> Count of orders about OrderDate between 1996-01 and 1996-03 by EmployeeID
--    Second_Quarter -> Count of orders about OrderDate between 1996-04 and 1996-06 by EmployeeID
--    Third_Quarter -> Count of orders about OrderDate between 1996-07 and 1996-09 by EmployeeID
--    Fourth_Quarter -> Count of orders about OrderDate between 1996-10 and 1996-12 by EmployeeID
------------------------------------------------------------------------------
  SELECT EmployeeID
        ,COUNT(CASE WHEN DATEPART(QQ, OrderDate) = 1 THEN 1 END) First_Quarter
        ,COUNT(CASE WHEN DATEPART(QQ, OrderDate) = 2 THEN 1 END) Second_Quarter
        ,COUNT(CASE WHEN DATEPART(QQ, OrderDate) = 3 THEN 1 END) Third_Quarter
        ,COUNT(CASE WHEN DATEPART(QQ, OrderDate) = 4 THEN 1 END) Fourth_Quarter
    FROM Orders 
   WHERE CONVERT(NCHAR(4), OrderDate, 112) = 1996
GROUP BY EmployeeID

GROUP BY를 통해서 EmployeeID별로 데이터를 GROUP해줍니다.

DATEPART를 통해서 1, 2, 3, 4분기별로 데이터의 수를 집계함수 COUNT를 사용해서 구했습니다.

- 결과 -

7. 

--  Use Order table
--    Select CustomerID and Freight_1996 and Freight_1997 columns
--    Freight_1996 = Sum of Freight about Orderdate in 1996 By CustomerID
--    Freight_1997 = Sum of Freight about Orderdate in 1997 By CustomerID
------------------------------------------------------------------------------
  SELECT CustomerID
        ,CONVERT(NUMERIC(12, 4)
        ,SUM(CASE DATEPART(YEAR, OrderDate) WHEN 1996 THEN Freight ELSE 0 END)) Freight_1996
        ,CONVERT(NUMERIC(12, 4), SUM(CASE DATEPART(YEAR, OrderDate) WHEN 1997 THEN Freight ELSE 0 END)) Freight_1997
    FROM Orders 
GROUP BY CustomerID

위와 마찬가지로 GROUP BY와 CASE문을 이용해서 1996년과 1998년의 Freight의 합을 구했습니다.

- 결과 -

8. 

--  Use Orders table
--    Select OrderYearMonth and OrderCount column
--    OrderYearMonth : If OrderDate in 1996 then OrderDate Change to YYYY else OrderDate Change to YYYYMM
--    OrderCount : Count of Order by OrderYearMonth
------------------------------------------------------------------------------
  SELECT CASE CONVERT(NCHAR(4), OrderDate, 112)
              WHEN 1996 THEN 1996
              WHEN 1997 THEN CONVERT(NCHAR(6), OrderDate, 112)
         END OrderYearMonth
        ,COUNT(*) OrderCount
    FROM Orders
   WHERE CONVERT(NCHAR(4), OrderDate, 112) IN (1996, 1997)
GROUP BY CASE CONVERT(NCHAR(4), OrderDate, 112)
              WHEN 1996 THEN 1996
              WHEN 1997 THEN CONVERT(NCHAR(6), OrderDate, 112)
         END

GROUP BY에 CASE문을 사용해서 조건을 걸어서 데이터를 가져올 수 있습니다.

- 결과 -

9.

--  Use Orders table
--    Select OrderDate in 1996
--    Select only CustType and OrderCount column
--    CustType : If CustomerID in ('VINET', 'TOMSP', 'HANAR') then "Special Cust" Else "Normal Cust"
--    OrderCount : Count of orders by custtype
------------------------------------------------------------------------------
  SELECT CASE 
             WHEN CustomerID IN ('VINET', 'TOMSP', 'HANAR') THEN 'Special Cust'
             ELSE 'Normal Cust'
         END CustType
        ,COUNT(*) OrderCount
    FROM Orders
   WHERE DATEPART(YEAR, OrderDate) = 1996
GROUP BY CASE 
             WHEN CustomerID IN ('VINET', 'TOMSP', 'HANAR') THEN 'Special Cust'
             ELSE 'Normal Cust'
         END

GROUP BY를 하는데 CASE문을 이용해서 CustomerID가 'VINET', 'TOMSP', 'HANAR'일 경우 'Special Cust'  나머지 경우 'Normal Cust'로 GROUP BY를 하였다.

- 결과 -

10. 

--  Use Orders table
--    Select OrderDate in 1996
--    Select EmployeeID and OrderCount, EmployeeGrade Column
--    OrderCount : Count of Order by EmployeeID
--    EmployeeGrade : 
--        OrderCount >= 30         : A+
--        OrderCount BETWEEN 20 AND 39     : A
--        OrderCount BETWEEN 15 AND 19     : B+
--        OrderCount BETWEEN 10 AND 14     : B
--        OrderCount <= 9         : C
------------------------------------------------------------------------------
  SELECT EmployeeID
        ,COUNT(*) OrderCount
        ,CASE
            WHEN COUNT(*) >= 40 THEN 'A+'
            WHEN COUNT(*) >= 20 THEN 'A'
            WHEN COUNT(*) >= 15 THEN 'B+'
            WHEN COUNT(*) >= 10 THEN 'B'
            ELSE 'C'
         END EmployeeGrade
    FROM Orders
   WHERE DATEPART(YEAR, OrderDate) = 1996
GROUP BY EmployeeID

CASE문을 사용해서 COUNT(*)의 값에 따라서 점수를 준다.

- 결과 -

여기까지 SQL문 실습 - CASE문(2)를 마치겠습니다.

다음 글에서 SQL문 실습 - CASE문(3)에서 이어서 CASE문에 대해서 알아보겠습니다.

SQL문 실습 - CASE문(1)

SQL문 실습 - CASE문(3)

참고 : SQL Server 기본 SQL문 교육