[TSQL] Fonction d'agrégat SUM et imbrication

David G. a écrit :

Bonjour,

je dois transformer des requêtes à destination Sybase v6 vers du SQL Server
2000.
J'ai plusieurs requêtes du type sum((Select sum(...) From ...)) qui bien
évidemment ne passent pas en SQL Server car on ne peut pas imbriquer les
fonctions d'agrégat.

Existe-t-il un moyen de contourner la chose ?

oui !

SELECT ...
FROM ...
GROUP BY ...
HAVING MAX ( ...) = (SELECT SUM( ...)
FROM ... )

Par exemple.

Ou encore :

SELECT SUM( ... )
FROM ( SELECT SUM( ...)
FROM ) T

Sans cas concret difficile de vous donner des exmeples plus précis !


A +



--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

>
Sans cas concret difficile de vous donner des exmeples plus précis !

Exemple concret simplifié :

SELECT

AGC.codeimput as Compte,
sum((Select sum(CDL.MtTotalHT)
from ContratDecompLigne as CDL
Where CDL.IdArticle = AGC.Idarticle and Month(CDL.dateFact) = 1)) as
[Janvier],

Sum((Select sum(CDL.MtTotalHT)
from ContratDecompLigne as CDL
Where CDL.IdArticle = AGC.Idarticle and Month(CDL.dateFact) = 2)) as
[Février],

-- jusqu'à décembre

sum((Select sum(CDL.MtTotalHT)
from ContratDecompLigne as CDL
Where CDL.IdArticle = AGC.Idarticle)) as [Totaux]

from ArticleGestCom as AGC
Group By AGC.codeimput
Having [Totaux] <> 0

En plus du problème de l'imbrication des fonctions d'agrégat, on voit
également apparaître un problème avec le surnommage ([Totaux]) qui ne peut
être réutilisé dans la clause Where.

J'ai également d'autres cas ou le surnommage me pose problème, par exemple
lors de la réutilisation au sein du SELECT

SELECT
(select count(*) from ArticleHisto where
ArticleHisto.IdContratParent=CliContrats.IdContratParent) as [Ligne1],
(select ArticleHisto.Montant from ArticleHisto where
ArticleHisto.IdContratParent=CliContrats.IdContratParent and [Ligne1] =
1), -- [Ligne1] est réutilisé ici
...
FROM ...

Je coince pour remettre en forme ces requêtes :-/

Auriez vous la définition des tables ?

A +

David G. a écrit :

Sans cas concret difficile de vous donner des exmeples plus précis !

Exemple concret simplifié :

SELECT

AGC.codeimput as Compte,
sum((Select sum(CDL.MtTotalHT)
from ContratDecompLigne as CDL
Where CDL.IdArticle = AGC.Idarticle and Month(CDL.dateFact) = 1)) as
[Janvier],

Sum((Select sum(CDL.MtTotalHT)
from ContratDecompLigne as CDL
Where CDL.IdArticle = AGC.Idarticle and Month(CDL.dateFact) = 2)) as
[Février],

-- jusqu'à décembre

sum((Select sum(CDL.MtTotalHT)
from ContratDecompLigne as CDL
Where CDL.IdArticle = AGC.Idarticle)) as [Totaux]

from ArticleGestCom as AGC
Group By AGC.codeimput
Having [Totaux] <> 0

En plus du problème de l'imbrication des fonctions d'agrégat, on voit
également apparaître un problème avec le surnommage ([Totaux]) qui ne peut
être réutilisé dans la clause Where.

J'ai également d'autres cas ou le surnommage me pose problème, par exemple
lors de la réutilisation au sein du SELECT

SELECT
(select count(*) from ArticleHisto where
ArticleHisto.IdContratParent=CliContrats.IdContratParent) as [Ligne1],
(select ArticleHisto.Montant from ArticleHisto where
ArticleHisto.IdContratParent=CliContrats.IdContratParent and [Ligne1] =
1), -- [Ligne1] est réutilisé ici
...
FROM ...

Je coince pour remettre en forme ces requêtes :-/

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

>
Auriez vous la définition des tables ?

A +

Globalement

ArticleGestcom
Identifiant (clé primaire)
codeImput

ContratDecompLigne
Identifiant (clé primaire)
IdArticle (clé étrangère vers ArticleGestcom.Identifiant)
DateFact

-----------

ArticleHisto
Identifiant (clé primaire)
IdContratParent (clé étrangère vers CliContrats.Identifiant)
Montant

CliContrats
Identifiant (clé primaire)
IdContratParent (clé étrangère vers CliContrats.Identifiant)

Merci.

David G. a écrit :

Auriez vous la définition des tables ?

A +

Globalement

ArticleGestcom
Identifiant (clé primaire)
codeImput

ContratDecompLigne
Identifiant (clé primaire)
IdArticle (clé étrangère vers ArticleGestcom.Identifiant)
DateFact

-----------

ArticleHisto
Identifiant (clé primaire)
IdContratParent (clé étrangère vers CliContrats.Identifiant)
Montant

CliContrats
Identifiant (clé primaire)
IdContratParent (clé étrangère vers CliContrats.Identifiant)

Merci.

Mzerci de les mettre sous forme d'ordre SQL CREATE TABLE !
je ne vais quand même pas passer mon temps à reproduire vos tables alors
qu'un simple clic droit script SQL le fait !

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

>
Merci de les mettre sous forme d'ordre SQL CREATE TABLE !
je ne vais quand même pas passer mon temps à reproduire vos tables alors
qu'un simple clic droit script SQL le fait !

Désolé, j'avais mal compris la demande.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ArticleGestCom](
[Identifiant] [bigint] NOT NULL,
[Annee] [smallint] NULL,
[CodeImput] [char](10) COLLATE French_CI_AS NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ContratDecompLigne](
[Identifiant] [bigint] NOT NULL,
[IdArticle] [bigint] NULL,
[DateFact] [datetime] NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ArticleHisto](
[Identifiant] [bigint] NOT NULL,
[IdContratParent] [bigint] NULL,
[Montant] [numeric](7, 2) NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CliContrats](
[Identifiant] [bigint] NOT NULL,
[IdContratParent] [bigint] NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ContratDecompLigne] WITH CHECK ADD CONSTRAINT
[ArticleGestCom_Identifiant] FOREIGN KEY([IdArticle])
REFERENCES [dbo].[ArticleGestCom] ([Identifiant])
GO
ALTER TABLE [dbo].[ArticleHisto] WITH CHECK ADD CONSTRAINT
[CliContrats_IdContratParentHisto] FOREIGN KEY([IdContratParent])
REFERENCES [dbo].[CliContrats] ([Identifiant])
GO
ALTER TABLE [dbo].[CliContrats] WITH CHECK ADD CONSTRAINT
[CliContrats_IdContratParent] FOREIGN KEY([IdContratParent])
REFERENCES [dbo].[CliContrats] ([Identifiant])

Encore désolé et merci pour tout.

j'ai l'impression que vos create table sont incomplet ou les requêtes
postées mal écrites :

Select sum(CDL.MtTotalHT), IdArticle, Month(CDL.dateFact) As Month
from ContratDecompLigne as CDL
GROUP BY IdArticle, Month(CDL.dateFact)

ne passe pas ! :
Serveur : Msg 207, Niveau 16, État 3, Ligne 1
'MtTotalHT' : nom de colonne incorrect.


A +


David G. a écrit :

Merci de les mettre sous forme d'ordre SQL CREATE TABLE !
je ne vais quand même pas passer mon temps à reproduire vos tables alors
qu'un simple clic droit script SQL le fait !

Désolé, j'avais mal compris la demande.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ArticleGestCom](
[Identifiant] [bigint] NOT NULL,
[Annee] [smallint] NULL,
[CodeImput] [char](10) COLLATE French_CI_AS NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ContratDecompLigne](
[Identifiant] [bigint] NOT NULL,
[IdArticle] [bigint] NULL,
[DateFact] [datetime] NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ArticleHisto](
[Identifiant] [bigint] NOT NULL,
[IdContratParent] [bigint] NULL,
[Montant] [numeric](7, 2) NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CliContrats](
[Identifiant] [bigint] NOT NULL,
[IdContratParent] [bigint] NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ContratDecompLigne] WITH CHECK ADD CONSTRAINT
[ArticleGestCom_Identifiant] FOREIGN KEY([IdArticle])
REFERENCES [dbo].[ArticleGestCom] ([Identifiant])
GO
ALTER TABLE [dbo].[ArticleHisto] WITH CHECK ADD CONSTRAINT
[CliContrats_IdContratParentHisto] FOREIGN KEY([IdContratParent])
REFERENCES [dbo].[CliContrats] ([Identifiant])
GO
ALTER TABLE [dbo].[CliContrats] WITH CHECK ADD CONSTRAINT
[CliContrats_IdContratParent] FOREIGN KEY([IdContratParent])
REFERENCES [dbo].[CliContrats] ([Identifiant])

Encore désolé et merci pour tout.

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

"SQLpro [MVP]" <brouardf@club-internet.fr> a écrit dans le message de news:
uPJWttwfGHA.4976@TK2MSFTNGP02.phx.gbl...

j'ai l'impression que vos create table sont incomplet ou les requêtes
postées mal écrites :

Select sum(CDL.MtTotalHT), IdArticle, Month(CDL.dateFact) As Month
from ContratDecompLigne as CDL
GROUP BY IdArticle, Month(CDL.dateFact)

ne passe pas ! :
Serveur : Msg 207, Niveau 16, État 3, Ligne 1
'MtTotalHT' : nom de colonne incorrect.

Effectivement :-/

CREATE TABLE [dbo].[ContratDecompLigne](
[Identifiant] [bigint] NOT NULL,
[IdArticle] [bigint] NULL,
[MtTotalHT] [numeric](7,2),
[DateFact] [datetime] NULL
PRIMARY KEY CLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[Identifiant] ASC
) ON [PRIMARY]
) ON [PRIMARY]