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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Group by month
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

vc56522
Starting Member

11 Posts

Posted - 04/10/2005 :  22:40:19  Show Profile
Could anyone please tell me what wrong with this code:
I am trying to group by month and year

Select DATENAME(mm,date_created) as Month, count(po_number) as Total_Po
from po_file
Group by DATENAME(mm,date_created), po_number
Order by DATENAME(mm,date_created), po_number

This code is only grouping by day
This is what my table look like:
date_created po_number
2004-02-04 123
2004-02-03 231
2005-02-05 344
2005-02-06 355
2005-02-07 366
2005-03-05 666
2005-03-05 567


This is what I want my result table to look like:
Month Total_po
Feb(04) 2
Feb(05) 3
Mar 2

rfrancisco
Yak Posting Veteran

USA
95 Posts

Posted - 04/10/2005 :  23:16:28  Show Profile  Visit rfrancisco's Homepage
Remove the po_number in your GROUP BY clause. It should look like this:

Select DATENAME(mm,date_created) as Month, count(po_number) as Total_Po
from po_file
Group by DATENAME(mm,date_created)
Order by DATENAME(mm,date_created)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/10/2005 :  23:16:52  Show Profile
Grouping by PO_NUMBER is causing your problem. You want something more like the code below. You can change the order and format to suit your needs.

select
	year(date_created) as Year, 
	datename(mm,date_created) as month,
	count(po_number) as total_po
from
	po_file
group by
	year(date_created),
	month(date_created),
	datename(mm,date_created)
order by by
	year(date_created),
	month(date_created),
	datename(mm,date_created)


CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/10/2005 :  23:19:43  Show Profile
It is rude to post your question on more than one forum;
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48224

CODO ERGO SUM
Go to Top of Page

vc56522
Starting Member

11 Posts

Posted - 04/11/2005 :  08:01:21  Show Profile

Thanks for your help
I didn't know I posted on another forum
Sorry about that.

quote:
Originally posted by Michael Valentine Jones

It is rude to post your question on more than one forum;
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48224

CODO ERGO SUM

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000