MYSQL 查询 - 按月获取总数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13152796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL query - getting totals by month
提问by NMF SDF
http://sqlfiddle.com/#!2/6a6b1
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');
回答by Salil
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 whereclause 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
回答by Er. Anurag Jain
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-01and 2012-12-17are 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
谢谢
回答by Devart
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()。
回答by Kamil
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.
我假设“销售数量”意味着行数。
回答by Asuquo12
Sometimes you might want the month names as Jan, Feb, Mar .... Dec possibly for a Chart likeFusionChart
有时您可能希望月份名称为 Jan、Feb、Mar .... Dec 可能用于像FusionChart这样的图表
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
