🚀 go-pugleaf

This is not an anomolie.  This is ANSI standard behavior, and SQL Server 6.0 is
 finally catching up.

The rule is:

"Every non-aggregate expression in a Select list MUST also be in the group by."

Notice I said expression, wider in scope than just column.

You might want to split this into two statements:  one to do the aggregates,
 store the result in a temp table, then a second to do the arithmetic.  That
 should be easier.

Never hesitate to split a complicated select into many simple selects.

Mike Culver
MS Certified SQL Server Trainer
Mercer Island, WA
mculver@halcyon.com

----------
From:   Ray Walther
Sent:   Monday, May 08, 1995 4:30
To:     Multiple recipients of list MSSQL-L
Subject:        Re: Help with a test

Works on my 4.21a server and not my 6.0 server.  I get the same error that
you mentioned below.  I guess that is just one of the many anomolies on the
new server.


>Would someone be willing to test the following query on a SQL Server 4.2x
>system please?
>This query is issued by the Microsoft Access upsizing tool and is failing on
>my 6.0 test system.  However I dont think it will work on a 4.2x system
>either.  The expected error is "d.status invalid in select list because it
>is not contained in the group by".  Adding d.status to the group by fixes
>the problem.
>
><snip>
>Select d.name, d.status,
>'Size (MB)' = (1+d.high-d.low)/512,
>'Used (MB)' = sum(u.size)/512,
>'Free (MB)' = ((1+d.high-d.low)-sum(u.size))/512
>>From master..sysusages u,
>master..sysdevices d,
>where d.cntrltype = 0
>and u.vstart between d.low and d.high
>group by d.name, d.high, d.low
><snip>
>
>thanks in advance
>
>--Adam
>
>
Barry