Group By problem, how to?

Hi, The following works:
Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
From SomeTable
Group By YEAR(TransDate)
The following gives an error, I have to group by Year and Month,
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
The problem is that TransDate is now used twice in the Group By clauseChris,
Why is this a problem? If you want one result row for
each year and month combination, you need to group on
year and month, or alternatively, use a single expression
for the year and month in both the select and group by
clauses.
The code you gave works correctly with no error
on SQL Server 2000 and 2005. An alternative with
a single group by item is given below also:
create table SomeTable (
TransDate datetime,
Amount money
)
insert into SomeTable values ('20050403', $100)
insert into SomeTable values ('20050703', $200)
insert into SomeTable values ('20060703', $300)
insert into SomeTable values ('20050708', $400)
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
go
Select
Total,
YEAR(TransMonth) As TheYear,
MONTH(TransMonth) As TheMonth
From (
Select
SUM(Amount) AS Total,
DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
FROM SomeTable
GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
) M
GO
drop table SomeTable
Steve Kass
Drew University
Chris Botha wrote:

>Hi, The following works:
>Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
>From SomeTable
>Group By YEAR(TransDate)
>The following gives an error, I have to group by Year and Month,
>Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
>From SomeTable
>Group By YEAR(TransDate), MONTH(TransDate)
>The problem is that TransDate is now used twice in the Group By clause
>
>|||Hi Steve, you are right, it works, must have been a typo somewhere.
Sorry for wasting your time (shrink, shrink, shrink).
Chris.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uIXBMjvOGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Chris,
> Why is this a problem? If you want one result row for
> each year and month combination, you need to group on
> year and month, or alternatively, use a single expression
> for the year and month in both the select and group by
> clauses.
> The code you gave works correctly with no error
> on SQL Server 2000 and 2005. An alternative with
> a single group by item is given below also:
> create table SomeTable (
> TransDate datetime,
> Amount money
> )
> insert into SomeTable values ('20050403', $100)
> insert into SomeTable values ('20050703', $200)
> insert into SomeTable values ('20060703', $300)
> insert into SomeTable values ('20050708', $400)
> Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
> From SomeTable
> Group By YEAR(TransDate), MONTH(TransDate)
> go
> Select
> Total,
> YEAR(TransMonth) As TheYear,
> MONTH(TransMonth) As TheMonth
> From (
> Select
> SUM(Amount) AS Total,
> DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
> FROM SomeTable
> GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
> ) M
> GO
> drop table SomeTable
> Steve Kass
> Drew University
> Chris Botha wrote:
>