group by

I've created a table containing columns date, cost, orders etc. I want to
write a query group by the month part of the date. Is it possible to write
it. if yes how. Any suggestion would be greatly appreciated.
regards
shineSelect col1,col2,col3,MONTH(datecol)
From SomeTable
Group by col1,col2,col3,MONTH(datecol)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"shine" <shine@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3FA1DE3D-DD75-442B-B969-2367E7406E48@.microsoft.com...
> I've created a table containing columns date, cost, orders etc. I want to
> write a query group by the month part of the date. Is it possible to write
> it. if yes how. Any suggestion would be greatly appreciated.
> regards
> shine|||u can get the month as
select datepart(m,getdate()) This_Month
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"shine" wrote:

> I've created a table containing columns date, cost, orders etc. I want to
> write a query group by the month part of the date. Is it possible to write
> it. if yes how. Any suggestion would be greatly appreciated.
> regards
> shine|||Two problems with the code:
(1) all the months get grouped together, regardless of the year, so the
data is meaningless
(2) You are not allowed to have a function in a GROUP BY in Standard
SQL, so this does not port.
I would use a Calendar table to get a year/month value, do a JOIN and
gorup by that.|||On 19 May 2005 08:44:25 -0700, --CELKO-- wrote:

> Two problems with the code:
> (1) all the months get grouped together, regardless of the year, so the
> data is meaningless
> (2) You are not allowed to have a function in a GROUP BY in Standard
> SQL, so this does not port.
> I would use a Calendar table to get a year/month value, do a JOIN and
> gorup by that.
A Calendar table could be real overkill in this case; it didn't sound as
though there are any data attached to specific dates, like there are in the
traditional holiday calendar table.
To get around both the problems identified above, and to show a possible
meaningful use for the grouping, use this construction:
SELECT col1, col2, col3, theYear, theMonth,
COUNT(*) "OrderCount", SUM(cost) "TotalCost"
FROM (
SELECT col1,col2,col3,YEAR(datecol) "theYear", MONTH(datecol) "theMonth"
FROM SomeTable
)
GROUP BY col1, col2, col3, theYear, theMonth|||Comments inline.
http://www.sqlserver2005.de
--
"--CELKO--" <jcelko212@.earthlink.net> schrieb im Newsbeitrag
news:1116517465.190810.201240@.o13g2000cwo.googlegroups.com...
> Two problems with the code:
> (1) all the months get grouped together, regardless of the year, so the
> data is meaningless
Even this was only a example, to show the teamwork of group functions with
the selection of oher columns. Due to the lack of DDL by the original poster
there was no clue what he wants to do. Sytanx exmaples doesnt alway make
sense since they are only syntax examples (due to the lack of DDL).

> (2) You are not allowed to have a function in a GROUP BY in Standard
> SQL, so this does not port.
Actually you are allowed, unless you mention the function also in your Group
by clause, if you are working on TSQL and you dont wanna port your code,
why should you bother about Standard SQL ?

> I would use a Calendar table to get a year/month value, do a JOIN and
> gorup by that.