[SQL] 조건에 맞는 값만 SUM/COUNT 하기

코드카타 96번 | LeetCode 1193 - Monthly Transactions

문제

Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

Return the result table in any order.

Input: 
Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
Output: 
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

접근 방법

state 컬럼의 값이 'approved'인 경우에만 그 값이 output 테이블의 approved_count, approved_total_amount에 적용되어야 한다.

풀이

state가 approved인 데이터의 값만 count하고 sum하기 위해 approved, approved_amount 칼럼을 새로 정의하고 WITH 절을 이용하여 계산하였다.

 WITH cte AS (SELECT *,
        IF(state = 'approved', 1, 0) approved,
        IF(state = 'approved', amount, 0) approved_amount
    FROM Transactions
)
SELECT SUBSTR(trans_date, 1, 7) month, country, 
    count(*) trans_count,
    SUM(approved) approved_count,
    SUM(amount) trans_total_amount,
    SUM(approved_amount) approved_total_amount
FROM cte
GROUP BY month, country

개선된 풀이

다른 사람의 풀이를 보다가 SUM() 함수 내부에 조건을 넣고, 그 조건에 대한 각 행의 값이 참이면 1, 거짓이면 0을 반환한다는 사실을 이용하여 서브쿼리 없이 output 테이블을 도출하는 쿼리를 발견하였다.

SELECT 
    LEFT(trans_date, 7) AS month,
    country, 
    COUNT(id) AS trans_count,
    SUM(state = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM((state = 'approved') * amount) AS approved_total_amount
FROM 
    Transactions
GROUP BY 
    month, country;

• approved_count는 위 원리를 바탕으로 COUNT()이 아닌 SUM()을 이용하였다.
• approved_total_amount도 마찬가지로 조건문으로 반환된 0, 1에 amount를 곱하는 방식으로 state가 approved인 행의 amount만 더하였다.