IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQLite Discussion :

MAX(SUM()) : Soucis de sélection


Sujet :

SQLite

  1. #1
    Futur Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    Juillet 2015
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur .NET

    Informations forums :
    Inscription : Juillet 2015
    Messages : 14
    Points : 6
    Points
    6
    Par défaut MAX(SUM()) : Soucis de sélection
    Bonjour.
    J'ai une requête a taper mais je n'y arrive pas.

    Je sais pas trop comment vous expliquer le problème, mais en vous donnant la requête que j'ai pré établie et le but vous comprendrez beaucoup plus facilement.

    La requête sémantiquement parlant : Le ou les genre(s) du ou des produit(s) le(s) plus acheté(s) en quantité.

    La requête SQL :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT numProduit, MAX(totalAchete), genre from (SELECT sum(quantite) as totalAchete, LesAchats.numProduit, genre
                            FROM LesAchats
                            JOIN LesProduits on LesAchats.numProduit = LesProduits.numProduit
                            GROUP BY LesAchats.numProduit);
    Le problème : Je ne dois avoir que le genre dans mon résultat. actuellement j'ai le numProduit (pas de soucis pour le retirer) et SURTOUT le MAX(totalAchete).

    Piste : je pense que la solution serais plutôt du coté d'un Having max(totalAchete) > = (Select ....) mais je n'ai pas réussi.

    Le schéma de la base : http://nsa38.casimages.com/img/2017/...1917685450.png

    Le fichier de la base est en PJ.

    Ne cherchez pas la logique ou autre, ceci m'est demandé dans le cadre de ma formation et les contraintes sont strictes : on ne veux QUE le genre.
    La question est donc : comment retirer le MAX(totalAchete) de mon résultat.

    Je vous remercie de votre lecture.

    EDIT : Je suis bete. Voici la solution que j'ai adopté
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT genre
    from (SELECT MAX(totalAchete), genre from (SELECT sum(quantite) as totalAchete, LesAchats.numProduit, genre
                            FROM LesAchats
                            JOIN LesProduits on LesAchats.numProduit = LesProduits.numProduit
                            GROUP BY LesAchats.numProduit));
    Je m'excuse pour ce post, mais je commencés a m’arracher les cheveux, pour un problème si basique ... enfin.

    Cordialement.
    Fichiers attachés Fichiers attachés

  2. #2
    Membre éprouvé
    Homme Profil pro
    Chef de projets retraité
    Inscrit en
    Juillet 2011
    Messages
    419
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Cher (Centre)

    Informations professionnelles :
    Activité : Chef de projets retraité
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2011
    Messages : 419
    Points : 1 096
    Points
    1 096
    Par défaut
    Bonjour,

    Je ne suis pas tout à fait d'accord.

    Avec les données de ta base je trouve savon comme genre le plus vendu....

    Parce que dans seconde sous requête, tu te fais pas de groupe donc le genre que tu trouves est un peu au hasard.

    Personnellement, j'ai utilisé une CTE (common table expression) qui donne le genre et le total acheté.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT p.genre, sum(a.quantite)
       FROM LesAchats a
            JOIN LesProduits p on a.numProduit = p.numProduit
       group by genre
    et là tu pourras voir que savon => 71 alors que tasse => 49 (comme crayon)
    Puis ensuite il faut récupérer le maximum et sortir le genre correspondant
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    with quantitepargenre ( genre, quantite) as
    (SELECT p.genre, sum(a.quantite)
       FROM LesAchats a
            JOIN LesProduits p on a.numProduit = p.numProduit
       group by genre)
    select genre from quantitepargenre
      where quantite = (select max(quantite) from quantitepargenre)
    Cordialement

  3. #3
    Futur Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    Juillet 2015
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur .NET

    Informations forums :
    Inscription : Juillet 2015
    Messages : 14
    Points : 6
    Points
    6
    Par défaut
    Bonjour,

    Merci de ta reponse pour commencer, je ne connaissais pas la synthaxe CTE ( qui resemble a une vue en faite ?).

    par contre, le genre le plus vendu ce n'est pas la même chose que le genre du produit le plus vendu.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT sum(quantite) as totalAchete, LesAchats.numProduit, genre
                            FROM LesAchats
                            JOIN LesProduits on LesAchats.numProduit = LesProduits.numProduit
                            GROUP BY LesAchats.numProduit
    Cette requête te permettra de faire la somme des quantité vendu pour chaque produit, et le gagnant est le produit 43 avec 26 unités vendus, et de genre tasse.

    Je me trompe quelque part ?

    Merci encore pour la CTE.

  4. #4
    Membre éprouvé
    Homme Profil pro
    Chef de projets retraité
    Inscrit en
    Juillet 2011
    Messages
    419
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Cher (Centre)

    Informations professionnelles :
    Activité : Chef de projets retraité
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2011
    Messages : 419
    Points : 1 096
    Points
    1 096
    Par défaut
    Je me trompe quelque part ?
    Non, c'est moi qui ait lu un peu vite

  5. #5
    Futur Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    Juillet 2015
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur .NET

    Informations forums :
    Inscription : Juillet 2015
    Messages : 14
    Points : 6
    Points
    6
    Par défaut
    Citation Envoyé par acaumes Voir le message
    Non, c'est moi qui ait lu un peu vite
    Non tu n'a pas lu trop vite, c'est juste que mon prof fait exprès de mettre des définitions de requêtes ou il faut faire très attention a la sémantique !
    C'est volontaire de sa part de porter a confusion.

    Par contre je veux bien de l'aide pour une autre requete. (attention a la sémantique lol !)

    REQUETE : Les noms des clients qui achètent uniquement des produits non notés.

    Ma proposition (division) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT DISTINCT nom FROM LesClients
    JOIN LesAchats on LesClients.numClient = LesAchats.numProduit
    WHERE numProduit IN (
    SELECT DISTINCT numProduit
    FROM LesProduits
    EXCEPT
    SELECT DISTINCT numProduit
    FROM LesNotes);
    Le probleme c'est que il suffit qu'il ai acheter un seul produit non noté pour qu'il apparaisse, alors que je voudrais que tout les produit qu'il a acheter ne soit pas noté.
    J'ai du mal a ecrire cette requete

    RESULTAT ATTENDU :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    Hemmings
    Batten
    Buford
    Ledermann

  6. #6
    Membre éprouvé
    Homme Profil pro
    Chef de projets retraité
    Inscrit en
    Juillet 2011
    Messages
    419
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Cher (Centre)

    Informations professionnelles :
    Activité : Chef de projets retraité
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2011
    Messages : 419
    Points : 1 096
    Points
    1 096
    Par défaut
    Bon Voici une proposition...

    Premièrement il faut distinguer les clients qui ont acheté des produits notés.
    Pour cela il faut joindre les notes et les achats.

    On fait donc une jointure gauche pour que les produits non notés soient tout de même dans la liste...
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select distinct  a.numclient,  a.numproduit, n.note from lesachats a
    left join lesnotes n on a.numproduit = n.numproduit
    order by a.numclient
    On peut maintenant additionner les notes et sachant que le null est une valeur spécifique, le résultat de la somme des notes sera null si tous les produits d'un client sont null
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select  a.numclient as numclient, sum(n.note) as Notation from lesachats a
            left join lesnotes n on a.numproduit = n.numproduit
         group by a.numclient
    Il ne reste plus qu'à sélectionner les clients qui vont bien avec une autre sous requête pour filtrer sur ceux qui ont une notation (somme des notes) à null
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    select c.nom from lesclients c
    where c.numclient in
    (select numclient from
        (select  a.numclient as numclient, sum(n.note) as Notation from lesachats a
            left join lesnotes n on a.numproduit = n.numproduit
         group by a.numclient)
      where notation is null
      )
    Avec cette requête je trouve bien le résultat attendu.

  7. #7
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique retraité
    Inscrit en
    Janvier 2007
    Messages
    15 021
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 67
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique retraité
    Secteur : Industrie

    Informations forums :
    Inscription : Janvier 2007
    Messages : 15 021
    Points : 40 935
    Points
    40 935
    Billets dans le blog
    62
    Par défaut
    Bonjour,

    j'arrive trop tard, bien que je m'y sois branché hier soir je n'ai pas trouvé le courage d'y répondre.
    Je suis d'accord avec acaumes sur les CTE c'est quand même bien plus lisible sauf que bien évidement, si le prof ne les a pas présentées il comprendra qu'il y a eu recherche (ce qui n'est pas mal en fait) et aide possible (ce qu'il ne veut pas !)

    @acaumes pour la dernière solution où l'on voit la démarche CTE et la solution "condensée" si peu facile à lire et donc à maintenir
    MVP Embarcadero
    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Rio, Sidney), D11 (Alexandria), D12 (Athènes)
    SGBD : Firebird 2.5, 3, SQLite
    générateurs États : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Windows 11, Ubuntu, Androïd

  8. #8
    Futur Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    Juillet 2015
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur .NET

    Informations forums :
    Inscription : Juillet 2015
    Messages : 14
    Points : 6
    Points
    6
    Par défaut
    Merci acaumes, je comprend pas trop d'ou provient mon erreur (a part une fausse jointure entre numProduit et numClient aie aie aie la fatigue ^^).

    En quoi ma division n'est pas bonne ?

    J'ai voulu faire la différence entre les produits et les produits notés pour recup les non noté, puis chercher les clients qui ont commandé ces produits (déjà j'ai un soucis sémantique dans cette approche car ici on dis "uniquement" donc c'est exclusivement des produits non noté, pas juste 1).

    En tous cas merci.

    SergioMaster, non ce n'est pas un soucis que d'avoir de l'aide si c'est en recherchant de l'information. Je demande pas a se que quelqu'un me fasse toute mes requêtes sans même que j'essaye un minimum. Rechercher et demander de l'aide n'est pas mal vu.

  9. #9
    Membre éprouvé
    Homme Profil pro
    Chef de projets retraité
    Inscrit en
    Juillet 2011
    Messages
    419
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Cher (Centre)

    Informations professionnelles :
    Activité : Chef de projets retraité
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2011
    Messages : 419
    Points : 1 096
    Points
    1 096
    Par défaut
    Bonjour,

    En quoi ma division n'est pas bonne ?
    La requête avec la soustraction relationnelle te donne la liste des produits non notés

    Tu recherches donc dans ta requête les clients qui ont acheté un produit non noté (et tu les auras tous).


    Je reviens sur ta première requête, tu utilises une fonction d'aggrégation sans mettre les colonnes non aggrégées dans le group by
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT MAX(totalAchete), genre from ...
    cette forme n'est autorisée que par peu de SGBD et n'est pas vraiment conforme à SQL.

    Cordialement

  10. #10
    Futur Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    Juillet 2015
    Messages
    14
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Développeur .NET

    Informations forums :
    Inscription : Juillet 2015
    Messages : 14
    Points : 6
    Points
    6
    Par défaut
    Citation Envoyé par acaumes Voir le message
    B
    cette forme n'est autorisée que par peu de SGBD et n'est pas vraiment conforme à SQL.
    Tout à fait, merci.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT genre
    from (SELECT MAX(totalAchete), genre from (SELECT sum(quantite) as totalAchete, LesAchats.numProduit, genre
                            FROM LesAchats
                            JOIN LesProduits on LesAchats.numProduit = LesProduits.numProduit
                            GROUP BY LesAchats.numProduit, genre));
    dans le Select MAX il nécessaire de le précisé aussi ? la fonction d’agrégation MAX travail sur un résultat déjà aggregé par le group by de la requête interne (la requête sum).

    Sinon je ne vois pas quoi mettre dans la clause GROUP BY de la requête Select max().
    Car si je met une clause group by dans la requête MAX, alors il devra me trouver le MAX pour chaque produit, ou pour chaque genre, ou autre. Sauf que ce n'est pas mon but, je souhaite juste le max de la requête interne sum.

  11. #11
    Membre éprouvé
    Homme Profil pro
    Chef de projets retraité
    Inscrit en
    Juillet 2011
    Messages
    419
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Cher (Centre)

    Informations professionnelles :
    Activité : Chef de projets retraité
    Secteur : Transports

    Informations forums :
    Inscription : Juillet 2011
    Messages : 419
    Points : 1 096
    Points
    1 096
    Par défaut
    la fonction d’agrégation MAX travail sur un résultat déjà aggregé par le group by de la requête interne (la requête sum).
    Oui bien sûr. Cependant bien que le résultat que tu obtiens corresponde rien ne garantit que la valeur de la colonne genre que tu obtiens soit celle qui est en relation dans la même ligne que le max.
    https://www.sqlite.org/lang_select.html
    If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression



    Pour obtenir un résultat à coup sûr il faut
    1. utiliser les fonctions de classement (RANK OVER) mais SQLITE ne les supporte pas.
    2. écrire deux fois la sous requête un fois pour retrouver la valeur maximale et une seconde fois pour retrouver le genre
    3. Utiliser un CTE comme je l'ai fait ce qui permet de n'écrire la sous requête qu'une seule fois.


    Voici ce que pourrait donner la requête sans CTE (Point 2)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT genre from
      (
      SELECT sum(a.quantite) as totalAchete, a.numProduit, p.genre
        FROM LesAchats a
             JOIN LesProduits p on a.numProduit = p.numProduit
        GROUP BY a.numProduit, p.genre
      )
    where totalachete =
      (select max(totalachete) from
         (SELECT sum(a.quantite) as totalAchete, a.numProduit, p.genre
            FROM LesAchats a
                 JOIN LesProduits p on a.numProduit = p.numProduit
            GROUP BY a.numProduit, p.genre
        )
        )
    A noter qu'il est possible de ne pas intégrer le genre dans la seconde sous requête car il n'intervient pas dans le calcul de la quantité achetée -(relation 1-1 entre produit et genre) et donc de ne pas faire la jointure ce qui ferait ceci
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT genre from
      (
      SELECT sum(a.quantite) as totalAchete, a.numProduit, p.genre
        FROM LesAchats a
             JOIN LesProduits p on a.numProduit = p.numProduit
        GROUP BY a.numProduit, p.genre
      )
    where totalachete =
      (select max(totalachete) from
         (SELECT sum(a.quantite) as totalAchete, a.numProduit
            FROM LesAchats a
            GROUP BY a.numProduit
        )
        )

    Avec CTE (point 3)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    with quantiteparproduitgenre ( quantite, produit, genre) as
    (  SELECT sum(a.quantite) as totalAchete, a.numProduit, p.genre
        FROM LesAchats a
             JOIN LesProduits p on a.numProduit = p.numProduit
        GROUP BY a.numProduit, p.genre
    )
    select genre from quantiteparproduitgenre
      where quantite = (select max(quantite) from quantiteparproduitgenre)

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Souci pour sélection multiple
    Par abatjour dans le forum Langage SQL
    Réponses: 6
    Dernier message: 28/12/2010, 18h56
  2. pb requete max sum sur 2 criteres.
    Par btsgreta77 dans le forum Requêtes
    Réponses: 2
    Dernier message: 30/04/2009, 17h01
  3. Sélection d'enregistrements - MAX(SUM())
    Par Nightwulf dans le forum Requêtes et SQL.
    Réponses: 5
    Dernier message: 20/01/2008, 07h07
  4. Sélection d' une valeur max depuis 2 tables
    Par ipeteivince dans le forum Requêtes
    Réponses: 4
    Dernier message: 13/05/2005, 18h37
  5. Réponses: 14
    Dernier message: 01/12/2004, 16h20

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo