当只有日期时间字段时，如何按月和年分组? [英] How do I group by month and year when only having a datetime field?

问题描述

我有一个表模式，它本质上是一堆带有日期时间字段的交易信息

I have a table schema which is essentially a bunch of transaction info with a datetime field

TRANSACTION (< transactionid >, amount, when)

我需要每月生成总计SUM(amount)的交易，但是我对我的分组依据感到困惑. SQL中的每一行都应包含每月总计(因此，应在1月9日，2月9日... 2010年1月等一行).我在想可能必须通过代码生成表，但是想了解是否有一种方法可以使用SQL来解决这个问题.

I need to generate a monthly total of transactions, which is SUM(amount), but I stumped by what do I group by. Each row from SQL should contain the monthly total (so one row for Jan 09, Feb 09....Jan 2010 and so on). I am thinking that I may have to generate the table by code, but would like to learn if there is a way to resolve this using SQL.

任何帮助将不胜感激！ (使用MySQL 5.3，PHP5)

Any help would be appreciated! (Using MySQL 5.3, PHP5)

推荐答案

您需要按提取内容进行分组.

You need to group by extracts.

SELECT 
    SUM(amount)
FROM 
    transaction
GROUP BY 
    EXTRACT(MONTH FROM when),
    EXTRACT(YEAR FROM when)

如果您需要这些列，那么

And if you need those columns, then

SELECT
    EXTRACT(MONTH FROM when) as month, 
    EXTRACT(YEAR FROM when) as year, 
    SUM(amount)
FROM 
    transaction
GROUP BY 
    month,
    year

当然，您可以附加ORDER BY并使用短名称:

Of course you can append ORDER BY and use short names too:

SELECT 
    EXTRACT(MONTH FROM when) as month, 
    EXTRACT(YEAR FROM when) as year, 
    SUM(amount)
FROM 
    transaction
GROUP BY 
    month, 
    year
ORDER BY 
    year DESC, 
    month DESC

这篇关于当只有日期时间字段时，如何按月和年分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！