Group by clause - 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 2005 Forums

Transact-SQL (2005)

Group by clause

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2010-09-22 : 18:05:35

Create table DBO.TEST
( vID int ,
vdate datetime )

Insert into dbo.TEST
select 1, GETDATE()-2
union
Select 2, GETDATE()-2
union
Select 3, GETDATE()-4
union
Select 4, GETDATE()-5
union
Select 5, GETDATE()-5
union
Select 6, GETDATE()-6
union
Select 7, GETDATE()-7

Select CONVERT( varchar, vdate,101) ,COUNT(1)
from DBO.TEST
group by CONVERT( varchar, vdate,101)
order by 1 desc

How can i get the output ?

Date count
09/19/2010 0

Chandragupta Mourya

Takhyashila

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-09-22 : 19:06:09

Not following you exactly, can you explain more what you are looking to do.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-22 : 19:16:51

You have to run his code to see what he means. He wants to show count(*) of 0 when that particular date doesn't exist. I think he needs a table of dates to do it to see where the gaps are.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-23 : 07:24:04

declare @TEST table
( vID int ,
vdate datetime )

Insert into @TEST
select 1, GETDATE()-2
union
Select 2, GETDATE()-2
union
Select 3, GETDATE()-4
union
Select 4, GETDATE()-5
union
Select 5, GETDATE()-5
union
Select 6, GETDATE()-6
union
Select 7, GETDATE()-7

declare @st as datetime
declare @end as datetime

select @st = MIN(vdate),@end = MAX(vdate) from @TEST
print @st
print @end
;with timeslots as
(

select DATEADD(DAY,count1-1,@st) as ts_f from
(
select row_number() over (order by id desc) [count1] from sysobjects
) as a where count1<(DATEDIFF(day, @st,@end))+2
)

Select CONVERT( varchar, ts_f,101) ,(select COUNT(*) from @TEST where CONVERT( varchar, ts_f,101) = CONVERT( varchar, vdate,101))
from timeslots
group by CONVERT( varchar, ts_f,101)
order by 1 desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:10:44

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources