在 SQLite 中按月分组
我有一个包含交易的 SQLite 数据库,每个交易都有一个 price 和一个 transDate.
I have an SQLite database which contains transactions, each of them having a price and a transDate.
我想检索按月分组的交易总和.检索到的记录应如下所示:
I want to retrieve the sum of the transactions grouped by month. The retrieved records should be like the following:
Price month
230 2
500 3
400 4
推荐答案
当你按 MONTH 分组时,它也应该检查 YEAR 总是好的
select SUM(transaction) as Price,
DATE_FORMAT(transDate, "%m-%Y") as 'month-year'
from transaction group by DATE_FORMAT(transDate, "%m-%Y");
对于 SQLITE
select SUM(transaction) as Price,
strftime("%m-%Y", transDate) as 'month-year'
from transaction group by strftime("%m-%Y", transDate);
相关文章