LC 1193每月交易 IMonthly Transactions I　中等

Transactions 表含国家、状态 state、金额与日期；按月份、国家统计总笔数、通过笔数、总金额与通过金额。

思路 DATE_FORMAT 截月份；SUM(条件) 与 SUM(IF(条件,金额,0))。

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
       country,
       COUNT(*) AS trans_count,
       SUM(state = 'approved') 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;

数据表：Transactions　·　关键字：按月份、国家统计总笔数、通过笔数、总金额与通过金额