grouping by months

Hello everyone

starting from this example table:

code qnt date

aaa 1 21/01/2006
abc 2 24/01/2006
aaa 3 27/01/2006
asd 1 11/03/2006
wde 2 16/03/2006
aaa 1 18/03/2006

I'd like to select records grouping by months and adding quantities (qnt column) for similar codes on each moth period.

Result expected is:

code qnt month

aaa 4 01

abc 2 01

asd 1 03

wde 2 03

aaa 1 03

Is there an efficient way to do this?

Any example?

Thanks a lot

WHat aout:

SELECT code,SUM(qnt),MONTH(date)
FROM SomeTable
GROUP BY code,MONTH(Date)

HTH, Jens Suessmeyer.

|||

Gee!

I didn't realize it was so obvious..

Thanks!

|||

Would ne nice if you could mark the topic as solved that it doesn��t appear on my (any other) watch lists anymore, thanks.

-Jens-

|||

Select Sum(Qty), DatePart(m, date) from Table group by DatePart(m, date)

That should get you started.

edit: Sorry, didn't realize someone had helped.

group number

i have the following data in the database:
hk a aa aaa
hk b bb bbb
hk c cc ccc
uk d dd ddd
uk e ee eee
us f ff fff
and they are displayed in a matrix like below
hk a aa aaa
b bb bbb
c cc ccc
uk d dd ddd
e ee eee
us f ff fff
i would like to add a number to any new row like below
1 hk a aa aaa
b bb bbb
c cc ccc
2 uk.....
3Us...
how can i do this...i have tried rownumber and runningvalue but it doesnot
work
please help~~
thank you so much in advanceTake a look at
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/21/199.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:32D440E6-EC85-4C1C-A4EA-913BD543C7F6@.microsoft.com...
> i have the following data in the database:
> hk a aa aaa
> hk b bb bbb
> hk c cc ccc
> uk d dd ddd
> uk e ee eee
> us f ff fff
> and they are displayed in a matrix like below
> hk a aa aaa
> b bb bbb
> c cc ccc
> uk d dd ddd
> e ee eee
> us f ff fff
> i would like to add a number to any new row like below
> 1 hk a aa aaa
> b bb bbb
> c cc ccc
> 2 uk.....
> 3Us...
> how can i do this...i have tried rownumber and runningvalue but it doesnot
> work
> please help~~
> thank you so much in advance