CODEWITHSUNDEEP
mysqlsql
Rahul shah
Rahul shah

Reputation: 185

How to get count of Number of rows by month using date in Mysql

I have a database which looks like

Table1

ID      Date
1       2018-01-01 15:04:03
2       2018-01-02 18:06:05
3       2018-01-03 23:21:12
4       2018-02-01 15:04:03
5       2018-02-02 18:06:05
6       2018-02-03 23:21:12

I want the count based on Month either in JAN-18 format or in 2018-01 format.

Required output:

Month   Count
JAN-18  3
FEB-18  3

Upvotes: 1

Views: 2042

Answers (5)

David Faber
David Faber

Reputation: 12495

You might use the DATE_FORMAT() function:

SELECT DATE_FORMAT(Date, '%Y-%m'), COUNT(*)
  FROM Table1
 GROUP BY DATE_FORMAT(Date, '%Y-%m')

%m in the mask will give the month's number (01-12) and %Y is the 4-digit year; use the following to get the other format you mentioned:

SELECT UPPER(DATE_FORMAT(Date, '%b-%y')), COUNT(*)
  FROM Table1
 GROUP BY UPPER(DATE_FORMAT(Date, '%b-%y'))

%b is the month's name abbreviated, but capitalized rather than all-uppercase; %y is the 2-digit year.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

The format yyyy-mm is sortable, so use this to group by:

select date_format(date, 'Y-m') as month, count(*)
from table1
group by month
order by month;

(Grouping by the alias name is not valid in standard SQL, but in MySQL it is and it gets the query very readable.)

Upvotes: 0

D. Braun
D. Braun

Reputation: 508

select DATE_FORMAT(Date, '%b-%y'), count(*) from table1 group by YEAR(Date), MONTH(Date)
  • %b: Abbreviated month name (Jan..Dec)
  • %y: Year, numeric (two digits)

see: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Upvotes: 1

James
James

Reputation: 3015

This is what you want:

SELECT CONCAT(UPPER(DATE_FORMAT(Date, '%b')),'-',year(Date)), count(*)
FROM table1
GROUP BY CONCAT(UPPER(DATE_FORMAT(Date, '%b')),'-',year(Date))

Upvotes: 0

jarlh
jarlh

Reputation: 44795

GROUP BY the year and the month:

select year(Date), month(Date), count(*)
from table1
group by year(Date), month(Date)

Formatting the output shouldn't be that tricky, but I don't know MySQL...

Try concat(cast(year(Date) as char(4)),'-',cast(month(Date) as char(2))).

Upvotes: 1

Related Questions