[Leet 1193] Monthly Transactions 1

DATE_FORMAT으로 날짜 형식 맞추기

그리고 GROUP BY 후 집계함수 내 CASE - WHEN 이나 IF 절로 원하는 조건에 맞게 통계내기

 

이거 두 개가 핵심이다

 

---

SELECT
    DATE_FORMAT(trans_date,'%Y-%m') 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 YEAR(trans_date), MONTH(trans_date), country

SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    COUNT(IF(state='approved', 1, NULL)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state='approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY YEAR(trans_date), MONTH(trans_date), country