SQL Server Forums
Profile | Register | 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.06 seconds. Powered By: Snitz Forums 2000