MYSQL查询 - 按月获得总计

http://sqlfiddle.com/#!2/6a6b1

The scheme is given above.. all I want to do is get the results as the total of sales/month... the user will enter a start date and end date and I can generate (in PHP) all the month and years for those dates. For example, if I want to know the total number of "sales" for 12 months, I know I can run 12 individual queries with start and end dates, but I want to run only one query where the result will look like:

上面给出了该方案..我想做的就是得到结果作为销售/月的总数...用户将输入开始日期和结束日期，我可以生成（在PHP中）所有月份和年份那些日期。例如，如果我想知道12个月的“销售”总数，我知道我可以运行12个具有开始和结束日期的单个查询，但我想只运行一个查询，结果如下：

Month     numofsale
January - 2  
Feb-1
March - 23
Apr - 10

and so on...

等等...

or just a list of sales without the months, I can then pair it to the array of months generated in the PHP ...any ideas...

或者只是没有月份的销售清单，然后我可以将它与PHP中生成的月份数组配对......任何想法......

Edit/schema and data pasted from sqlfiddle.com:

从sqlfiddle.com粘贴的编辑/架构和数据：

CREATE TABLE IF NOT EXISTS `lead_activity2` (
  `lead_activity_id` int(11) NOT NULL AUTO_INCREMENT,
  `sp_id` int(11) NOT NULL,
  `act_date` datetime NOT NULL,
  `act_name` varchar(255) NOT NULL,
  PRIMARY KEY (`lead_activity_id`),
  KEY `act_date` (`act_date`),
  KEY `act_name` (`act_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;

INSERT INTO `lead_activity2` (`lead_activity_id`, `sp_id`, `act_date`, `act_name`) VALUES
(1, 5, '2012-10-16 16:05:29', 'sale'),
(2, 5, '2012-10-16 16:05:29', 'search'),
(3, 5, '2012-10-16 16:05:29', 'sale'),
(4, 5, '2012-10-17 16:05:29', 'DNC'),
(5, 5, '2012-10-17 16:05:29', 'sale'),
(6, 5, '2012-09-16 16:05:30', 'SCB'),
(7, 5, '2012-09-16 16:05:30', 'sale'),
(8, 5, '2012-08-16 16:05:30', 'sale'),
(9, 5,'2012-08-16 16:05:30', 'sale'),
(10, 5, '2012-07-16 16:05:30', 'sale');

5 个解决方案

#1

SELECT DATE_FORMAT(date, "%m-%Y") AS Month, SUM(numofsale)
FROM <table_name>
WHERE <where-cond>
GROUP BY DATE_FORMAT(date, "%m-%Y")

Check following in your fiddle demo it works for me (remove where clause for testing)

在你的小提琴演示中检查它对我有用（删除where子句进行测试）

SELECT DATE_FORMAT(act_date, "%m-%Y") AS Month, COUNT(*)
FROM lead_activity2
WHERE <where-cond-here> AND act_name='sale'
GROUP BY DATE_FORMAT(act_date, "%m-%Y")

It returns following result

它返回以下结果

MONTH   COUNT(*)
07-2012 1
08-2012 2
09-2012 1
10-2012 3

#2

You can try query as given below

您可以尝试查询，如下所示

select  SUM(`SP_ID`) AS `Total` , DATE_FORMAT(act_date, "%M") AS Month, Month(`ACT_DATE`) AS `Month_number` from `lead_activity2`  WHERE `ACT_DATE` BETWEEN '2012-05-01' AND '2012-12-17' group by Month(`ACT_DATE`)

Here 2012-05-01 and 2012-12-17 are date input from form. and It will be return you the sum of sales for particular month if exist in database.

2012-05-01和2012-12-17是从表单输入的日期。如果存在于数据库中，它将返回特定月份的销售总额。

thanks

谢谢

#3

SELECT YEAR(act_date), MONTH(act_date), COUNT(*)

FROM lead_activity2

GROUP BY YEAR(act_date), MONTH(act_date)

For getting data by month or any other data based on column you have to add GROUP BY.

要按月获取数据或根据列获取任何其他数据，您必须添加GROUP BY。

You can add many columns or calculated values to GROUP BY.

您可以向GROUP BY添加许多列或计算值。

I assume that "num of sales" means count of rows.

我假设“销售数量”表示行数。

#4

Try this query -

试试这个查询 -

SELECT
  MONTH(act_date) month, COUNT(*)
FROM
  lead_activity2
WHERE
  YEAR(act_date) = 2012 AND act_name = 'sale'
GROUP BY
  month

Check WHERE condition if it is OK for you - act_name = 'sale'.

如果可以，请检查WHERE条件 - act_name ='sale'。

If you want to output month names, then use MONTHNAME() function instead of MONTH().

如果要输出月份名称，则使用MONTHNAME（）函数而不是MONTH（）。

#5

Sometimes you might want the month names as Jan, Feb, Mar .... Dec possibly for a Chart likeFusionChart

有时您可能希望月份名称为Jan，Feb，Mar ... Dec可能为ChartFusionChart

SELECT DATE_FORMAT(date, "%M") AS Month, SUM(numofsale)
FROM <Table_name>
GROUP BY DATE_FORMAT(date, "%M")

Results would look like this on table

结果在桌面上看起来像这样

MONTH   COUNT(*)
Jul       1
Aug       2
SEP       1
OCT       3

#1

SELECT DATE_FORMAT(date, "%m-%Y") AS Month, SUM(numofsale)
FROM <table_name>
WHERE <where-cond>
GROUP BY DATE_FORMAT(date, "%m-%Y")