Question

1193. 每月交易 I

English Version

题目描述

表：Transactions

+---------------+---------+
| Column Name   | Type  |
+---------------+---------+
| id      | int   |
| country     | varchar |
| state     | enum  |
| amount    | int   |
| trans_date  | date  |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 ["approved", "declined"] 之一。

 

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以 任意顺序 返回结果表。

查询结果格式如下所示。

 

示例 1:

输入：
Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state  | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US    | approved | 1000   | 2018-12-18 |
| 122  | US    | declined | 2000   | 2018-12-19 |
| 123  | US    | approved | 2000   | 2019-01-01 |
| 124  | DE    | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month  | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US    | 2       | 1        | 3000         | 1000          |
| 2019-01  | US    | 1       | 1        | 2000         | 2000          |
| 2019-01  | DE    | 1       | 1        | 2000         | 2000          |
+----------+---------+-------------+----------------+--------------------+-----------------------+

解法

方法一：分组求和

我们可以先按照月份和国家分组，然后利用 COUNT 和 SUM 函数分别求出每个分组的事务数、已批准的事务数、总金额和已批准的总金额。

# Write your MySQL query statement below
SELECT
  DATE_FORMAT(trans_date, '%Y-%m') AS month,
  country,
  COUNT(1) 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 1, 2;