Grouping rows

sioleabha · July 6, 2004, 5:06pm

I have a database of journal entries with dates, which I would like to group by year, like so:

2000 (140)
2001 (92)
2002 (62)
2003 (235)
2004 (159)

The problem is, I don’t actually have a year column – just a timestamp. Is there a way to pull the year from the timestamp and group by that? I’ve been using an array to do it, but every time I add a year I have to edit the code to add the new year in.

Michelle, wife & homeschooling mommy to 4
The Mommy Database – #!/usr/bin/mom

if at first you don’t succeed, try, try again. then quit. no use being a damn fool about things.
– w. c. fields

lig · July 6, 2004, 11:18pm

check out date(). it allows the input of a timestamp for conversion.

sioleabha · July 7, 2004, 2:17am

Yes, but I can’t use it in the MySQL query, can I? If I had a year column I could do this:

SELECT year, COUNT(*) AS year_count FROM journal GROUP BY year

I can get the year from the timestamp, but I can’t group it, see?

Michelle, wife & homeschooling mommy to 4
The Mommy Database – #!/usr/bin/mom

So do not fear, for I am with you; do not be dismayed, for I am your God. I will strengthen you and help you; I will uphold you with my righteous right hand.
– Isaiah 41:10

lig · July 7, 2004, 3:19am

http://dev.mysql.com/doc/mysql/en/Date_ … tions.html

There is a YEAR function in MySQL

sioleabha · July 8, 2004, 10:10pm

Thanks for that link! I used the FROM_UNIXTIME() function, like so:

SELECT FROM_UNIXTIME( timestamp, ‘%Y’ ) AS link_year, COUNT(*) AS num FROM journal WHERE FROM_UNIXTIME( timestamp, ‘%Y’ ) != $year GROUP BY link_year DESC

That got all of the years except the one you’re currently looking at. Thanks again!

Michelle, wife & homeschooling mommy to 4
The Mommy Database – #!/usr/bin/mom

And we know that all things work together for good to them that love God, to them who are the called according to his purpose.
– Romans 8:28