#94. [LeetCode / MySQL] 1193. Monthly Transactions I

문제

 

입력 코드

1. 

SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) 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 month, country

 

2. 

SELECT
    SUBSTR(trans_date, 1, 7) AS month,
    country,
    COUNT(*) AS trans_count,
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY month, country

 

코드 설명

#SELECT #DATE_FORMAT #COUNT #SUM #IF #GROUP BY #SUBSTR #CASE WHEN 

 

 

문제 출처

Monthly Transactions I - LeetCode