select user accounts by month

Forum Moderators: open

Message Too Old, No Replies

select user accounts by month

mysql query month totals

bleak26

7:17 am on Aug 13, 2008 (gmt 0)

Hi,i am trying to retrive the total user accounts for each month in the current year from my user accounts table for a graph. i have a registration date field and i can extract this infomation easily for a single month, but i cannot work out how to get all the months totals in just one query.

Thankyou in advance for any help you can offer.

phranque

8:53 am on Aug 13, 2008 (gmt 0)

SELECT COUNT(*) AS total_accounts, MONTH(reg_date) AS month FROM table_name WHERE YEAR(reg_date) = YEAR(NOW()) GROUP BY MONTH(reg_date)

bleak26

9:22 am on Aug 13, 2008 (gmt 0)

Thankyou Frankie for the quick response and good answer. now with the answer in hand i have realised that i need the query to give me the last 12 months even if there are no results for that month, please could you tell me how to do that ?

eg:
jan = 5

feb = 2

march = 0

april = 5

phranque

9:35 am on Aug 13, 2008 (gmt 0)

not sure you can do that in a query.
you will probably have to fill in the empty months in your script.
also note that "the last 12 months" is not the same as "each month in the current year".
and to put a finer point on it, do you mean "the last 12 month names" or "the last 365 days"?
for example does "the last 12 months" (from today) include the end of august, 2007 or does it start with september, 2007?
all things to consider when you need the precise answer...