Select Count(*) per day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select Count(*) per day

Author

Topic

jwallz
Starting Member

14 Posts

Posted - 2005-09-07 : 12:52:11

I'm trying to write a query that will provide me with a total count of new registrations per day over the course of several days. I can't figure this out. I simply want 1 total number of new registrations for a particular day for say, each of the last 3 days. Only 3 rows s/b returned.I've tried several things and I keep getting multiple records per day, not totals. Can anyone help?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-07 : 13:24:55

try something like this:


select	dateadd(day, datediff(day, 0, YourDateColumn), 0) as [Day]
	,count(*) as [DayCount]
from	yourTable
Group by dateadd(day, datediff(day, 0, YourDateColumn), 0)

EDIT:
Here is a sample implementation of the above:


set nocount on
declare @tb table (i int, d datetime)
insert @tb
select 1, getdate() union all
select 1, getdate() union all
select 1, getdate()-1 union all
select 1, getdate()-1 union all
select 1, getdate()-2 union all
select 1, getdate()-2

select	convert(varchar,d,101) [Date]
	,c DateCount
from	(
	select	dateadd(day, datediff(day, 0, d), 0) d
		,count(*) c
	from	@tb
	Group by dateadd(day, datediff(day, 0, d), 0)
	) a

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources