Count based on date - 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

General SQL Server Forums

New to SQL Server Programming

Count based on date

Author

Topic

mrsoleil78
Starting Member

2 Posts

Posted - 2012-03-17 : 21:12:56

Hi All
I have the following table
01-03-2012 09:12:38 A
01-03-2012 10:15:23 B
01-03-2012 11:21:10 A
02-03-2012 08:38:34 A
02-03-2012 09:56:59 C
03-03-2012 14:45:29 B

I need to be able to go from the above table to the below table.
Date Room Count
01-03-2012 A 2
01-03-2012 B 1
02-03-2012 A 1
02-03-2012 C 1
03-03-2012 B 1

I tried the following
select t.date,t.room,count(t.room)as "count per room"
from tableA t
group by t.date,t.room

but I get the following table but the count is not right
Date room count per room
01-03-2012 09:12:38 A 1
01-03-2012 10:15:23 B 1
01-03-2012 11:21:10 A 1
02-03-2012 08:38:34 A 1
02-03-2012 09:56:59 C 1
03-03-2012 14:45:29 B 1

Any help will be greatly appreciated

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-17 : 21:24:17

If you convert to date rather than datetime in your select statement, you'll be able to group it by the dates as in your desired output like so:

select convert(date, Somedate), room, COUNT(*)
from YourTable
group by convert(date, Somedate), room
order by SomeDate, Room

mrsoleil78
Starting Member

2 Posts

Posted - 2012-03-17 : 21:56:11

Great stuff!

Thank you Flamblaster, it works!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-17 : 22:00:35

You're welcome :) Sidenote...this wouldn't work in SQL Server 2005 or lower...you'd have to approach a bit differently because the "date" date type didn't exist until 2008.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources