1193. Monthly Transactions I

https://leetcode.com/problems/monthly-transactions-i/description/

MySQL

SELECT DATE_FORMAT(trans_date, '%Y-%m') as month, country, COUNT(state) as trans_count,
 COUNT(CASE WHEN state LIKE 'approved' THEN 1 END) AS approved_count,
 SUM(amount) as trans_total_amount,
SUM(CASE WHEN state LIKE 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country

그룹 바이를 잘 사용해서 그룹화 시키고 조건에 맞게 계산을 해준다.

Oracle

SELECT TO_CHAR(trans_date, 'YYYY-MM') as month, country, COUNT(state) as trans_count,
 COUNT(CASE WHEN state LIKE 'approved' THEN 1 END) AS approved_count,
 SUM(amount) as trans_total_amount,
SUM(CASE WHEN state LIKE 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM Transactions
GROUP BY TO_CHAR(trans_date, 'YYYY-MM'), country

DATE_FORMAT 에서 TO_CHAR 로 변경