Group records by Month and Year

Question

I have a table

| inquiry_id | supplier_id | created             |
 ------------------------------------------------
| 1          | 2           | 2012-10-19 03:10:29 |
 ------------------------------------------------ 
| 2          | 2           | 2012-10-17 04:11:23 |
 ------------------------------------------------
| 3          | 2           | 2012-09-19 03:15:11 |
 ------------------------------------------------
| 4          | 2           | 2012-09-7 01:16:32  |
 ------------------------------------------------

I want to have a mysql result like,

| Month - Year | Total Inquiry |
--------------------------------
| 10-2013      | 2             |
--------------------------------
| 09-2013      | 2             |       
--------------------------------

How can I do this using MySQL?

Thanks @Blade0rz those links really works – iamnards Jul 10 '13 at 07:25 — iamnards, Jul 10 '13 at 07:25

score 6 · Answer 1 · answered Jul 10 '13 at 07:13

6

Use DATE_FORMAT() function for that:

SELECT DATE_FORMAT(created,'%m-%Y') AS Month_Year
     , COUNT(inquiry_id) AS Total_Inquiry
FROM Table1
GROUP BY DATE_FORMAT(created,'%m-%Y')

See this SQLFiddle

answered Jul 10 '13 at 07:13

Himanshu Jansari

30,115
28
106
129

score 0 · Answer 2 · edited Jul 10 '13 at 07:29

0

You could use:

SELECT DATE_FORMAT(created,'%m-%Y'), COUNT(inquiry_id) 
FROM tbl
GROUP BY YEAR(created), MONTH(created)

Here's the SQLFiddle

edited Jul 10 '13 at 07:29

Mark

7,818
14
46
78

answered Jul 10 '13 at 07:15

bizzehdee

19,145
9
43
73

Group records by Month and Year

2 Answers2

See this SQLFiddle