What I am trying to do is to query this table and find out how many people registered in each month between Jan-Dec 2005. In other words, I want to be able to produce the following output:
At the moment, with my limited SQL skills, I am only able to do this by creating 12 separate queries as follows:
select count(*) as counter
from table1
where
registration_date <= '2005-01-31' AND
registration_date >= '2005-01-01';
select count(*) as counter
from table1
where
registration_date <= '2005-02-39' AND
registration_date >= '2005-02-01';
etc...
...
This approach seems very cumbersome. So I was wondering, is there a way I could achieve this by writing one SQL query instead of breaking it into 12 separate queries? Is there a better way of doing this than with the approach I am using?
select Month(registration_date) as n,
Monthname(registration_date) as m,
count(registration_date) as c
from table1
where Year(registration_date) = '2005'
GROUP BY m
ORDER BY n
Another way of doing this which would let you do it for all years would be:
SELECT
DATE(registration_date) AS RegistrationDate
, COUNT(UserID) AS NumberOfRegistrations
FROM
table1
GROUP BY
RegistrationDate
You can add any where filters you like to narrow down the results, as roger did above:
SELECT
DATE(registration_date) AS RegistrationDate
, COUNT(UserID) AS NumberOfRegistrations
FROM
table1
WHERE
YEAR(registration_date) = '2005'
GROUP BY
RegistrationDate
imminent, your query will return one row for the 12th, one row for the 13th, etc. – in other words, more than one row per month
roger, yours works really nicely in mysql, but will fail with a syntax error in any other database (incorrect GROUP BY), and will only return months which contain a date in the table
blank man, if you want all months, even those which have 0 dates, you need to LEFT OUTER JOIN to the data table from a table which has numbers 1 through 12 in it
select n
, monthname(
date_add('2937-12-01'
, interval n month)) as m
, count(registration_date) as c
from (
select 1 as n
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
) as months
left outer
join table1
on n = month(registration_date)
where year(registration_date) = 2005
group
by n, m
order
by n
Since the query would also most probably fail on named Date-Time functions anyway, I wrote it mysql-specific throughout.
The query will need a rewrite anyway, so why not exploit the target db engine fully? And I include in that mysql’s lax syntax: it has coding advantages of both speed and clarity - the important one, speed is just a bonus.
That said, Rudy’s memory table of month numbers, and more generic date_add() and month(), do make it highly portable and efficient - and the auto-fill of empty months is an excellent strategy.
