Problem with GROUP BY/COMPUTE : error message 8120

Hi,
I have this query ( it is Ok with Sybase SQLServer)
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
I want to migrate it under MS SQLServer 2000, but I have this error message
:
Serveur : Msg 8120, Niveau 16, tat 1, Ligne 1
Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
If I delete the last line, the query is Ok :
select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
,'Anne'=datepart(yy,DPSTVOI),'Priode'=
CPST,'Nombre'=count(NVOI)
from HREH3M
group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
The problem is with COMPUTE claude, but I can't understand why.
please help me to solve this.
thanks in advance
regards
LaurentWhat kind of error or warning appears when you drop that line? Apparently
syntax for COMPUTE is fine.
"Laurent CLAUDEL" wrote:

> Hi,
> I have this query ( it is Ok with Sybase SQLServer)
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> compute sum(count(NVOI)) by CPST,NVOI,datepart(yy,DPSTVOI)
>
> I want to migrate it under MS SQLServer 2000, but I have this error messag
e
> :
> Serveur : Msg 8120, Niveau 16, état 1, Ligne 1
> Column 'HREH3M.DPSTVOI' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> If I delete the last line, the query is Ok :
> select 'Voie'=NVOI,'Mois'=datepart(mm,DPSTVOI)
> ,'Année'=datepart(yy,DPSTVOI),'Période
'=CPST,'Nombre'=count(NVOI)
> from HREH3M
> group by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> order by CPST,NVOI,datepart(yy,DPSTVOI),datepart(
mm,DPSTVOI)
> The problem is with COMPUTE claude, but I can't understand why.
> please help me to solve this.
> thanks in advance
> regards
> Laurent
>
>|||if I drop the last line (COMPUTE), there is no more error.
"Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
> What kind of error or warning appears when you drop that line? Apparently
> syntax for COMPUTE is fine.
> "Laurent CLAUDEL" wrote:
>|||But I want a sum by Year, so i have to keep the COMPUTE clause
"Laurent CLAUDEL" <laurent.claudel@.steria.com> a crit dans le message de
news: OhOqg8Y1FHA.1108@.TK2MSFTNGP14.phx.gbl...
> if I drop the last line (COMPUTE), there is no more error.
> "Enric" <Enric@.discussions.microsoft.com> a crit dans le message de news:
> B27D7A22-9880-4A31-9335-D0C77C2DED83@.microsoft.com...
>|||I suggest you don't use COMPUTE / COMPUTE BY unless it's essential to
maintain Sybase compatibility. COMPUTE is legacy stuff that was
deprecated long ago. Take a look at CUBE / ROLLUP in Books Online -
it's a much more powerful feature.
David Portas
SQL Server MVP
--sql