We help IT Professionals succeed at work.

sorting mysql query by month, year for date column in varchar type d-M-Y

global_expert_advice
global_expert_advice used Ask the Experts™
on
hi

I m having an issue of sorting my table queries by month and year and group by part.
Actually the column field is varchar type like 13-Jun-2011
 Now i want to sort the result by months and year...

so that i get total count by months or month n year or only by year...

Jun 2011 - 40
Jul 2011 -65

or
2011 - 90
2012 - 100

my current query is somewhat like this

select refid,posted_on,count(*) as total from listing where refid='BS2121'

thanks...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>for date column in varchar type d-M-Y

you can do the montly part:
select right(yourfield, 8), count(*) from yourtable group by  right(yourfield, 8) 

Open in new window

however, if you want to do be "ordered" by the proper month/year, this will be a bit more tricky.
select right(yourfield, 8), count(*) 
from yourtable 
group by  right(yourfield, 8) 
order by convert( datetime, '01-' + right(yourfield, 8) , 106) 

Open in new window


the yearly part is simple:
select right(yourfield, 4), count(*) from yourtable group by  right(yourfield, 4) order by right(yourfield, 4) 

Open in new window


see more about date/times:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
Most Valuable Expert 2011
Top Expert 2016

Commented:
If you follow the principles outlined here (using the ISO-8601 standard date format) your work will instantly get easier!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I see I overlooked the "mysql" part here.
so my "cast" part to get "ordered" results will not work for MySQL (only MS SQL)
and I do agree 200% that a varchar field is the wrong choice, in first place.
Most Valuable Expert 2011
Top Expert 2016

Commented:
@angelIII: I'll see your 200% and raise you 200%

;-)

~Ray
hi angel

ur queries working fine except the 2nd one
 "ordered" by the proper month/year,
when using getting syntax error
right syntax to use near ''01-' + right(posted_on, 8) , 106) LIMIT 0, 30' at line 4

query : select right(posted_on, 8), count(*)
from listing
group by  right(posted_on, 8)
order by convert( datetime, '01-' + right(posted_on, 8) , 106)


any idea wats wrong
when i use this query

select right(posted_on, 8), count(*)
from listing
group by  right(posted_on, 8) order by  right(posted_on, 4)

i get... below result but months are not in right order...
right(posted_on, 8)      count(*)
Sep-2010      183
Nov-2010      13296
Oct-2010      15422
Dec-2010      220
Feb-2011      38310
Apr-2011      2
Jan-2011      23122
Jul-2011      20362
Jun-2011      17290
Nov-2011      1
Mar-2011      14269
Dec-2011      1
Sep-2011      2685
Aug-2011      32379
Oct-2011      29
May-2011      17443
Feb-2012      326
Jan-2012      55
Mar-2012      2428
Apr-2012      1328
Most Valuable Expert 2011
Top Expert 2016

Commented:
@global_expert_advice: Please make this easier on yourself.

1. Use ALTER TABLE to add a new column that conforms to the MySQL DATETIME data type and the ISO-8601 standard.
2. SELECT the VARCHAR column that has your existing date values.
3. Use strtotime() and date('c') to reformat the date so that it complies with the standard.
4. UPDATE each row to insert the ISO-8601 standard date value into the new column.

This is a one-time action that will help you forever avoid wasted time on questions like this!  All your date selections and calculations will suddenly work the right way.

Best of luck with it, ~Ray
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi!

The easiest way to do this is to use the function STR_TO_DATE :)
See the manual for further info
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

Then the query would look like this

select STR_TO_DATE(posted_on,  '%M-%Y' ), count(*)
from listing
group by  STR_TO_DATE(posted_on,  '%M-%Y' )

Regards,
   Tomas Helgi
thanks will check and let u know..
@TomasHelgi:  i tried urs.. was giving null in place of months..

i made little change to urs

select STR_TO_DATE(right(posted_on, 8),  '%M-%Y' ) as month, count(*)
from listing
group by  month

This is now giving me perfectly... except one issue, the date is showing 00, as mysql puts 00 for invalid dates. and as i did right(posted_on,8) because of which date gone..
so any idea how to change the 00 of each month to last date of particular month.

month      count(*)
2010-09-00      183
2010-10-00      15422
2010-11-00      13296
2010-12-00      220
2011-01-00      23122
2011-02-00      38310
2011-03-00      14269

thanks
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi

That's easy ;)
Use the function LAST_DAY(date) and add that to your previous date using DATE_ADD

some thing like this
DATE_ADD(STR_TO_DATE(right(posted_on, 8),  '%M-%Y' ), INTERVAL LAST_DAY(STR_TO_DATE(right(posted_on, 8),  '%M-%Y' ) ) DAYS)

Look at the manual for more info
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Regards,
    Tomas Helgi
HI
Thanks i manage to do this by using below query

select
LAST_DAY(STR_TO_DATE(right(posted_on, 8), '%M-%Y')) as month,
count(*)
from listing
group by  month
Nice help
Thanks
Theo KouwenhovenApplication Consultant

Commented:
What about next code:
right(posted_on, 4) = order by Year
left(right(posted_on, 8), 3) = Month


Order by right(posted_on, 4),
Case left(right(posted_on, 8), 3) 
	when 'Jan' then 1
	when 'Feb' then 2
	when 'Mar' then 3
	when 'Apr' then 4
	when 'May' then 5
	when 'Jun' then 6
	when 'Jul' then 7
	when 'Aug' then 8
	when 'Sep' then 9
	when 'Oct' then 10
	when 'Nov' then 11
	when 'Dec' then 12
	Else 99 end

Open in new window