Grouping rows


#1

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


#2

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


#3

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


#4

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

There is a YEAR function in MySQL


#5

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! :D


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