CODEWITHSUNDEEP
sqlsqlite
Answer_K
Answer_K

Reputation: 235

Sql: How to get the total monthly income and expenses for a certain year

this is table record:

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

How to get monthly statistics from year?

for example :

{
   "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

Upvotes: 0

Views: 434

Answers (1)

ekochergin
ekochergin

Reputation: 4129

You can use case statement in sum group function to determine if it is an income or a spending line

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);

Upvotes: 2

Related Questions