在SQLi中按月分组

Question

我有一个SQLite数据库，其中包含事务，每个事务都有price和transDate。

我要检索按月份分组的事务的总和。检索到的记录应如下所示：

Price    month
230        2
500        3
400        4

Answer 1

当您按月分组时总是很好，它还应该检查年份

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

Answer 2

您可以在月初分组：

select  date(DateColumn, 'start of month')
,       sum(TransactionValueColumn)
from    YourTable
group by 
        date(DateColumn, 'start of month')

Answer 3

SELECT
    SUM(Price) as Price, strftime('%m', myDateCol) as Month
FROM
    myTable
GROUP BY
    strftime('%m', myDateCol)