Count of all record on each day

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

General SQL Server Forums

New to SQL Server Programming

Count of all record on each day

Author

Topic

scarr
Starting Member

4 Posts

Posted - 2010-11-17 : 09:09:02

Hi,

I have a Table with a date field containing Date+time I want a count of each day even if there are no entries on that day, is this possible?

Example of data:

14/11/2010 13:00:32 "Hello"
14/11/2010 13:00:56 "Hello"
14/11/2010 13:06:01 "Hello"
15/11/2010 22:45:00 "Hello"
15/11/2010 22:54:18 "Hello"
17/11/2010 00:33:12 "Hello"

Results I want:

14/11/2010 3
15/11/2010 2
16/11/2010 0
17/11/2010 1

Steve

P.S. Sorry for being a newbie!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-17 : 09:53:04

you can remove the time part and do the group by.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-17 : 10:09:48

create table #test
(
TestDate datetime,
TestVal varchar(25)
)

Insert into #test
values ('11/14/2010 13:00:32','Hello')

Insert into #test
values ('11/14/2010 13:00:56','Hello')

Insert into #test
values ('11/15/2010 13:00:32','Hello')

Insert into #test
values ('11/16/2010 13:00:32','Hello')

select Dpart,count(*)
from
(
select dateadd(day,datediff(day,0,TestDate),0) Dpart
from #test) as subtab
group by Dpart

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-17 : 10:17:16

pk_bhora's solution won't give any rows for dates with no entries. To do that you need to join to a date table such as Michaels - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-17 : 11:26:48