MAX sur un SUM

Version imprimable

MAX sur un SUM

Bonjour,
j'ai un petit souci, j'ai besoin de faire un MAX() sur un SUM() dans une requête comportant des jointures.

Voici le schéma des tables utilisées pour la requête :

SERVEUR (NUM_SERVEUR, NOM_SERVEUR)
COMMANDE (NUM_COMMANDE, NUM_TABLE, NUM_SERVEUR, DATE, PRIX_TOTAL)
CONCERNER(NUM_CONSO, NUM_COMMANDE, QUANTITE)
CONSOMMATION (NUM_CONSO, NOM_CONSO, PRIX_CONSO)

Ma requête doit sélectionner le serveur qui a vendu le plus de tel ou tel consommation (NOM_CONSO).

En essayant ceci, j'ai tous les serveurs avec la quantité totale pour la conso '7up' :
Code:

1 2 3 4 5 6 7 8 SELECT NOM_SERVEUR, SUM(QUANTITE) AS QTE FROM SERVEUR INNER JOIN COMMANDE ON SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR INNER JOIN CONCERNER ON COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE INNER JOIN CONSOMMATION ON CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO WHERE CONSOMMATION.NOM_CONSO = '7up' GROUP BY NOM_SERVEUR;
Mais je n'arrive pas à sélectionner seulement celui qui en a vendu le plus.
J'ai essayé ceci :

Code:

MAX(SUM(QUANTITE))

dans la première ligne mais ça ne marche pas.

J'ai essayé ensuite ceci :
Code:

1 2 3 4 5 6 7 8 9 SELECT NOM_SERVEUR, MAX(QTE) FROM (SELECT NOM_SERVEUR, SUM(QUANTITE) AS QTE FROM SERVEUR INNER JOIN COMMANDE ON SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR INNER JOIN CONCERNER ON COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE INNER JOIN CONSOMMATION ON CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO WHERE CONSOMMATION.NOM_CONSO = '7up' GROUP BY NOM_SERVEUR) GROUP BY NOM_SERVEUR;
Mais ça me donne toujours tous les serveurs avec leurs quantités totales.

Je voulais essayer ceci (identique au premier essai mais avec un having) :
Code:

1 2 3 4 5 6 7 8 SELECT NOM_SERVEUR, SUM(QUANTITE) AS QTE FROM SERVEUR INNER JOIN COMMANDE ON SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR INNER JOIN CONCERNER ON COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE INNER JOIN CONSOMMATION ON CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO WHERE CONSOMMATION.NOM_CONSO = '7up' GROUP BY NOM_SERVEUR HAVING...;
Mais je ne sais pas quoi mettre comme fonction et comme opérateur pour le HAVING

Merci d'avance à celui qui m'apportera un peu d'aide !

01/12/2015, 11h30
Waldar
Effectivement, sauf exception pour un éditeur de SGBD, on ne peut pas combiner deux opération d'agrégats au niveau de la même requête.
Deux questions toutefois me viennent à l'esprit avant de vous proposer une réponse :
1. Que souhaitez-vous afficher si deux serveurs ont la même quantité ?
2. Quel est votre SGBD ?
01/12/2015, 11h35
Grichka29

Merci de ton aide.

Si deux serveurs ont la même quantité, alors il faut que ces deux serveurs soient affichés.

J'utilise Oracle.
01/12/2015, 11h45
escartefigue

Bonjour,

Vous pouvez généraliser la solution en utilisant une fonction de fenêtrage. Ce qui vous permettra, pour chaque consommation, de connaitre le(s) serveur(s) qui a(ont) fait le plus de ventes
01/12/2015, 12h14
Grichka29

Merci de ton aide.

Je ne connais pas le fenêtrage, j'ai regardé un peu et je ne comprend pas tout et ça m'embrouille un peu.

Je suis un cour sur le SQL et j'ai des requêtes à effectuer sur une base. Je pense que mon formateur attends une solution plus proche de ce que j'ai essayé plus haut et je préfère m'en tenir à ça pour le moment. Je ne suis déjà pas super à l'aise avec le SQL, je suis plus débutant qu'autre chose, alors je ne souhaite pas me lancer dans des notions plus complexe qu'on a pas encore vu (et qu'on ne verra peut être pas), en tous cas pour l'instant.
Je pense qu'il ne doit pas manquer grand chose à mes essais. Je cherche toujours.

Auquel cas vous pouvez faire ainsi en utilisant une CTE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH TMP AS
  (SELECT NOM_SERVEUR, 
          SUM(QUANTITE) AS QTE 
   FROM SERVEUR
   INNER JOIN COMMANDE 
      ON SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR
   INNER JOIN CONCERNER 
      ON COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE
   INNER JOIN CONSOMMATION 
      ON CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO
   WHERE CONSOMMATION.NOM_CONSO = '7up' 
   GROUP BY NOM_SERVEUR)
 
SELECT NOM_SERVEUR, QTE
FROM TMP
WHERE QTE=(select max(QTE) from TMP)

Ça marche en effet !

En cherchant sur le web ce qu'était une CTE, j'ai trouvé un exemple qui m'a aidé à résoudre mon soucis à partir de ma requête initiale :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT NOM_SERVEUR, SUM(QUANTITE) AS QTE FROM SERVEUR INNER JOIN COMMANDE ON SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR INNER JOIN CONCERNER ON COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE INNER JOIN CONSOMMATION ON CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO WHERE CONSOMMATION.NOM_CONSO = '7up' GROUP BY NOM_SERVEUR HAVING SUM(QUANTITE) = ( SELECT MAX(QTE) FROM (SELECT SUM(QUANTITE) AS QTE, NOM_SERVEUR FROM SERVEUR INNER JOIN COMMANDE ON SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR INNER JOIN CONCERNER ON COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE INNER JOIN CONSOMMATION ON CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO WHERE CONSOMMATION.NOM_CONSO = '7up' GROUP BY NOM_SERVEUR));
Elle est moins concise que la tienne, mais c'est d'avantage ce que le formateur attend de nous vu qu'on a pas abordé la notion de CTE.
Mais j'ai compris que le CTE m'évitait de faire une sous-requête quasi identique pour la comparer à la requête principale. C'est pratique, surtout quand on a pas mal de jointure.

Je garde néanmoins ta solution, et la lui proposerait en supplément de la mienne pour lui permettre, peut être, d'aborder cette notion car je ne suis pas certain que ce soit prévu !

Merci de ton aide en tous cas, deux jours que ça me prenait la tête ! ;)

01/12/2015, 14h29
escartefigue

Il est rare qu'il n'y ait qu'une seule solution SQL pour résoudre un problème !

Le choix final dépend de pas mal d'éléments du contexte, le fait d'avoir vu la syntaxe en cours en est un.
En entreprise, ce seront le plus souvent des notions de facilité de maintenance, de stabilité des résultats et de performances qui emporteront la décision.

Notez aussi que le SGBD MySQL n'accepte pas (il a tort :mrgreen:) les CTE, il faut donc faire des requêtes imbriquées, c'est un peu moins pratique

Simplifiez votre sous-requête ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
SELECT MAX(SUM(QUANTITE)) OVER() AS MAXQTE
FROM SERVEUR
    INNER JOIN COMMANDE ON
      SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR
    INNER JOIN CONCERNER ON
      COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE
    INNER JOIN CONSOMMATION ON
      CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO
WHERE CONSOMMATION.NOM_CONSO = '7up' 
GROUP BY NOM_SERVEUR

Ca ira plus vite !

A +

une autre méthode essayer de faire trier par ordre décroissant totale, et de limiter Ta requête par un Top(1)

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT top(1) SUM(QUANTITE) AS MAXQTE
FROM SERVEUR
    INNER JOIN COMMANDE ON
      SERVEUR.NUM_SERVEUR = COMMANDE.NUM_SERVEUR
    INNER JOIN CONCERNER ON
      COMMANDE.NUM_COMMANDE = CONCERNER.NUM_COMMANDE
    INNER JOIN CONSOMMATION ON
      CONCERNER.NUM_CONSO = CONSOMMATION.NUM_CONSO
WHERE CONSOMMATION.NOM_CONSO = '7up' 
GROUP BY NOM_SERVEUR
order by MAXQTE desc

10/12/2015, 16h43
SQLpro

Non, cette requête est totalement fausse !
En effet un agrégat ne sort pas systématiquement une seule ligne. Il peut y avoir des doublons par ex æquo....
TOP est un opérateur spécifique à SQL Server et n'est absolument pas relationnel (résultat hasardeux !)

A +