tricky problem for SQL wizards

[Henry Koren]

This problem would appear to be simple, yet I cannot figure out how to do it.

I have a table called member that contains all of my mailing list members.
This query is supposed to show how many people signed up to the mailing list
on each day. The results of this query are sent to a graph generator
component.

Here's how I do it now

I run this query for each day that I want to see how many people signed up:

SELECT Count(dateupdated) AS Counter FROM member where convert(char(12),
dateupdated, 112) = convert(char(12), dateadd(day, -@days, getdate()), 112)

This works, but I have to run it for EACH day.... so I have to run 30 of
these queries if I want to find out how many people have signed up in the
last 30 days. I just increment the @days variable for each day I want to find.

My question is, how can you create a single query that will produce
a result like this:

day counter
------ ----------
2/1/99 1
2/2/99 15
2/3/99 12
2/4/99 3
2/5/99 5
2/6/99 1
2/7/99 0
2/8/99 0

Thanks people!

[Greg]

how about using 'group by' clause?
for example:
select count(*) as Counter
from member
group by dateupdated

--or, you may need to use:
-- group by convert(char(8), dateupdated, 101)


On 2/23/99 2:11:20 PM, Henry Koren wrote:
> This problem would appear to be simple, yet I cannot figure out how to do
> it.

I have a table called member that contains all of my mailing list
> members.
This query is supposed to show how many people signed up to the
> mailing list
on each day. The results of this query are sent to a graph
> generator
component.

Here's how I do it now

I run this query for
> each day that I want to see how many people signed up:

SELECT
> Count(dateupdated) AS Counter FROM member where
> convert(char(12),
dateupdated, 112) = convert(char(12), dateadd(day,
> -@days, getdate()), 112)

This works, but I have to run it for EACH
> day.... so I have to run 30 of
these queries if I want to find out how
> many people have signed up in the
last 30 days. I just increment the
> @days variable for each day I want to find.

My question is, how can you
> create a single query that will produce
a result like this:

day
> counter
------ ----------
2/1/99 1
2/2/99 15
2/3/99
> 12
2/4/99 3
2/5/99 5
2/6/99 1
2/7/99 0
2/8/99 0

Thanks people!