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

Old Forums

CLOSED - General SQL Server

Group by month

Author

Topic

vc56522
Starting Member

11 Posts

Posted - 2005-04-10 : 22:40:19

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

95 Posts

Posted - 2005-04-10 : 23:16:28

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)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-10 : 23:16:52

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-10 : 23:19:43

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

vc56522
Starting Member

11 Posts

Posted - 2005-04-11 : 08:01:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources