Group by Month - 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

Transact-SQL (2000)

Group by Month

Author

Topic

enak
Starting Member

34 Posts

Posted - 2006-04-13 : 15:13:22

I have a table that I need to get a count of the records that have entered each month. What is the syntax to do this?

select Count(*), max(int_date), min(int_date) from interview
group by int_date

That is what I tried but it didn't give me what I wanted.

Thanks,
enak

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 15:29:09

Try the following 2


select Count(*), Datepart(m,int_date) from interview 
group by Datepart(m,int_date)
Order by Datepart(m,int_date)


Select A.[Month], A.[Records] from
(Select DATENAME ( Month , int_date ) as [Month], Month(int_date) as [No Use], count(*) as [Records] from interview 
Group by Month(int_date), DATENAME ( Month , int_date ) ) as A Order by A.[No Use]

Srinika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-13 : 17:05:30

[code]select
-- First day of month
[Interview Month] =
dateadd(month,datediff(month,0,int_date),0),
[Interview Count] = count(*)
from
interview
group by
-- First day of month
dateadd(month,datediff(month,0,int_date),0)
order by
-- First day of month
dateadd(month,datediff(month,0,int_date),0)[/code]

CODO ERGO SUM

magicmegabytes
Starting Member

1 Post

Posted - 2007-10-21 : 23:08:56

Srinika

Thanks for your post. Solutions #1 did it for me. I've been working on this line for 6 hours now.

Dave

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-21 : 23:13:39

Michael's solution is much better; don't just group on a month number. Read this:

http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources