What a pig's ear I've made of my earlier post

Hmm, perhaps I didn't explain myself too well on the problem I'm having
with my database in my last post over a week ago. There's a table, which
contains a timestamp field, amongst others. How would I write a query
that does the following:

1. For each year, count all the records for that given year. There's
approximately 6 years' worth.
2. Within each year, for each month, count all the records that
matches the month (i.e. Feburary 2006).

How would I go about achieving that?
--
http://www.munted.org.uk

Take a nap, it saves lives.

Alex Buell wrote:
> Hmm, perhaps I didn't explain myself too well on the problem I'm
> having with my database in my last post over a week ago. There's a
> table, which contains a timestamp field, amongst others. How would I
> write a query that does the following:
>
> 1. For each year, count all the records for that given year. There's
> approximately 6 years' worth.
> 2. Within each year, for each month, count all the records that
> matches the month (i.e. Feburary 2006).
>
> How would I go about achieving that?

For the year totals you can do:
SELECT year( entry ) , count( * )
FROM `guestbook`
GROUP BY year( entry )

For the month totals you can do:

SELECT concat_ws( '-', year( entry ) , month( entry ) ) , count( * )
FROM `guestbook`
GROUP BY concat_ws( '-', year( entry ) , month( entry ) ) ))

or

SELECT left( entry, 7 ) , count( * )
FROM `guestbook`
GROUP BY left( entry, 7 )