Sql:如何获得某一年的每月收入和支出总额

Question

这是一个表记录:

id    date         type       amount
1     2020-08-03   income     88
2     2020-09-11   spending   -120
3     2020-09-16   income     200
4     2020-11-05   income     95
5     2020-11-30   spending   35

如何从年获得每月统计数据?

例如:

{
"2021-11": {income: 500, spending: -800}
"2021-10": {income: 200, spending: -500}
"2021-09": {income: 800, spending: -300}
"2021-08": {income: 900, spending: -200}
}

ps: Sqlite

Answer 1

您可以在sum组函数中使用case语句来确定它是收入行还是支出行

select strftime('%Y-%m', date_col),
sum(case when type = 'income' then amount else 0 end) income,
sum(case when type = 'spending' then amount else 0 end) spending
from test_table
group by strftime('%Y-%m', date_col);