力扣SQL——每月交易Ⅰ

这道题主要考察聚合函数的使用
首先是日期的转换可以使用
DATE_FORMAT(trans_date, ‘%Y-%m’)进行具体的某一个转换成对应的月
然后是count 配合if的统计使用

# Write your MySQL query statement below
# count(if(state='approved',1,0)) as approved_count,
# 上面的语句是错的，因为聚合函数是count，本质上会对所有的值都进行计数，所以要置成null
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 month,country;