在SQLite中按月分组

小编典典

在SQLite中按月分组

sql

我有一个SQLite数据库，其中包含交易，每个交易都有一个价格和一个 transDate 。

我想检索按月分组的交易总和。检索到的记录应类似于以下内容：

Price    month
230        2
500        3
400        4

阅读 239

2021-03-10

共1个答案

小编典典

当您按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);

2021-03-10