February 28, 2008 at 5:44 am
I have select with aggregate column where I do multiply and stuff. In SQL 2005 I don't need full aggregate column in GROUP BY clause and I can have partial in GROUP BY and it works.
But SQL2000 it fails if I use partial aggregate function with message that
"Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
i.e. SQL 2005
select a.a1,
a.a2 * (a.a3 - a.a4)
from a a
GROUP BY a.a1, a.a3 - a.a4
Doesn't need whole aggregate column while same query fails on SQL 2000 with above error and to make it work
GROUP BY a.a1, a.a2 * (a.a3 - a.a4)
Full aggregate column required.
Any idea if this is improvement in SQL 2005?
February 28, 2008 at 7:27 am
I would not call this an improvement by any means. I would call it bad coding practice. SQL should never let you include columns in aggregate functions that do not exist in the group by clause.
Do you have the ddl and sample data for the table you are testing? My query in SQL 2005 returns an error, as it should.
This is the test data I am using:
declare @t table(
a1 int,
a2 int,
a3 int,
a4 int
)
insert into @t
select 1,1,1,1 union
select 2,2,2,2 union
select 3,3,3,3 union
select 4,4,4,4
select a.a1,
a.a2 * (a.a3 - a.a4)
from @t a
GROUP BY a.a1, a.a3 - a.a4
February 28, 2008 at 8:03 am
I agree with Adam. you should not be able to add those columns in if they are not GROUPed.
February 28, 2008 at 8:30 am
Something to this extent. I don't have the schema as I am investigating this issue from Siebel side where same query works connecting to SQL 2005 and failed SQL 2000.
I agree it should be bug rather than improvement but I wasn't sure.
create table a (a1 int,
a2 real,
a3 numeric (13,2),
a4 numeric (13,2))
select a.a1,
a.a2 * (a.a3 - a.a4)
from a a
group by a.a1, a.a3 - a.a4
February 28, 2008 at 9:06 am
I tried that on sQL 2005 SP2.
Msg 8120, Level 16, State 1, Line 6
Column 'a.a2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 9:19 am
I tried that on sQL 2005 SP2.
Msg 8120, Level 16, State 1, Line 6
Column 'a.a2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Same here and I am on SP2. I recieved the error as intended. This problem may have been resolved in SP 2.
Amu is your SQL 2005 on SP1?
February 29, 2008 at 3:42 pm
My SQL 2005 on SP1 but it fails on SQL 2005 as well. Except it works from Siebel. I am looking into Siebel. Thank you all.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply