mysql - SQL 查询 - 从第一天到今天按月分组

Question

我有一组不同分支机构每天的销售额记录。例如

Date             Amount      Branch
01/01/2014         30          A
01/01/2014         30          B
01/02/2014         40          A
01/02/2014         40          B
01/03/2014         30          A
01/03/2014         30          B
up to feb,mar,apr,may,jun,jul,aug

我想要实现的是根据今天的日期按月对记录进行分组。

例如，今天是 08/11/2014。范围应该是一个月的第一天“1”然后我会选择今天是 11。所以所有月份的范围是 1-11。请参见下面的示例。

Date Range for query monthly
01/01/2014-01/11/2014
02/01/2014-02/11/2014
03/01/2014-03/11/2014
04/01/2014-04/11/2014
05/01/2014-05/11/2014
06/01/2014-06/11/2014
07/01/2014-07/11/2014
08/01/2014-08/11/2014

将此日期范围分组并获得总销售额的总和。

请帮忙

Answer 1

这应该完成大部分工作:

  SELECT MONTH(date), SUM(amount)
    FROM table_name
   WHERE DAY(date) <= DAY(CURDATE())
     AND date >= YEAR(CURDATE())
GROUP BY MONTH(date);

更新

对于 3 个字母的月份标签，您可能还需要一个 ORDER BY 来确定:

  SELECT DATE_FORMAT(date,'%b'), SUM(amount)
    FROM table_name
   WHERE DAY(date) <= DAY(CURDATE())
     AND date >= YEAR(CURDATE())
GROUP BY MONTH(date)
ORDER BY MONTH(date);