This is what I finally figured out, which works just perfectly. select count(date) as count, substring(date,8) as year from stats group by year;
Thanks to all for the input. -----Original Message----- From: Joe Wollard [mailto:[EMAIL PROTECTED] Sent: October 29, 2006 12:15 AM To: Beauford Cc: PHP Subject: Re: [PHP] Query question Look into the MySQL YEAR() function to extract the year from a specific date. Start here: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html On 10/28/06, Beauford <[EMAIL PROTECTED]> wrote: > > I posted this here as I figured I would need to manipulate this using PHP. > The code below doesn't quite work because the date is in the format of > 05/05/2006. So I am getting totals for each date, not each year. I > need the totals for each year, regardless of the day or month. This is > why I figured I'd need to use PHP to maybe put it in an array first or > something. > > Thanks > > -----Original Message----- > From: Joe Wollard [mailto:[EMAIL PROTECTED] > Sent: October 28, 2006 10:30 PM > To: Ed Lazor > Cc: Beauford; PHP > Subject: Re: [PHP] Query question > > Agreed, this should go to a MySQL list. But in the spirit of helping I > think the following should give you a good starting point. > > SELECT `year`, COUNT(`year`) AS `count` FROM `tbl` GROUP BY `year` ASC > > > On 10/28/06, Ed Lazor <[EMAIL PROTECTED]> wrote: > > > > Use the mysql list :) > > > > > > On Oct 28, 2006, at 3:01 PM, Beauford wrote: > > > > > Hi, > > > > > > I have a MySQL database with a date field and a bunch of other > > > fields. The date field is in the format - 01/01/2006. What I want > > > to do is query the database and create a table that shows just the > > > year and how many instances of the year there is. I have been > > > taxing my brain for a simple solution, but just not getting it. > > > Any suggestions? > > > > > > Thanks > > > > > > Example output. > > > > > > Year Count > > > > > > 2002 5 > > > 2003 8 > > > 2004 9 > > > 2005 15 > > > 2006 22 > > > > > > ps - I get this information sent to me and I can't change any of > > > the data. I just enter it in the db and then hopefully do the > > > query on it. > > > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: > > http://www.php.net/unsub.php > > > > > > -- > PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: > http://www.php.net/unsub.php > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
