MYSQL query - getting totals by month

Question

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:

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...

Edit/schema and data pasted from 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');

I see create tables and insert dummy data code, what have you tried for the 'select' statement? — cctan, Oct 31 '12 at 07:04

Salil · Accepted Answer · 2012-10-31T07:12:48.757

34

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)

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

edited Oct 31 '12 at 07:12

answered Oct 31 '12 at 07:04

Salil

46,566
21
122
156

You can use `DATE_FORMAT(act_date, "%M-%Y")` for `July-2012` Ref:- http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format – Salil Oct 31 '12 at 07:24
4

It will not display in the result the months where sales number is zero – ursuleacv Oct 23 '15 at 18:58
1

why not `GROUP BY Month`? – törzsmókus Feb 07 '19 at 17:27
This will display the total sales by month in all year, however if anyone need to display it in a specific year, just add `YEAR(act_date) = 2012` in the `` – Snowbases Dec 19 '19 at 20:34

score 2 · Answer 2 · answered Oct 31 '12 at 07:16

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.

thanks

Devart · Answer 3 · 2012-10-31T07:10:30.700

1

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'.

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

edited Oct 31 '12 at 07:10

answered Oct 31 '12 at 07:05

Devart

119,203
23
166
186

well i tired this SELECT EXTRACT(MONTH FROM act_date) as month, EXTRACT(YEAR FROM act_date) as year, COUNT(*) FROM lead_activity2 WHERE act_name="sale" GROUP BY month, year ORDER BY year DESC, month DESC – Apurva Oct 31 '12 at 07:13
dev your query works....but i guess the solution i posted was a more complete one as it seems to have been doing the sorting and stuff..... will select yours – Apurva Oct 31 '12 at 07:16

score 0 · Answer 4 · answered Oct 31 '12 at 07:06

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.

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

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

score 0 · Answer 5 · answered Sep 13 '17 at 19:15

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

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

MYSQL query - getting totals by month

5 Answers5

Linked