LeetCode - 1193. Monthly Transactions I (MySQL)

Medium, SQL - SELECT

RunTime : 540 ms

문제

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.

The query result format is in the following example.

풀이

• 조건이 많았던 SELECT문 작성
• 4번째 필드에서 COUNT가 아닌 SUM을 사용하는 것이 포인트였다.

SELECT DATE_FORMAT(trans_date, '%Y-%m') as month, country,
COUNT(id) as trans_count,
SUM(IF(state = 'approved', 1, 0)) as approved_count,
SUM(amount) as trans_total_amount,
SUM(IF(state = 'approved', amount, 0)) as approved_total_amount
FROM Transactions
GROUP BY 1, 2