1. #1
    Nouveau Candidat au Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    novembre 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Lot (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : novembre 2017
    Messages : 7
    Points : 1
    Points
    1

    Par défaut Débutant statitiques avg()

    Bonjour,

    Je dispose de la table en pièce-jointe sur laquelle je dois réaliser des requêtes statistiques dans le cadre d'une étude marché immobilier.

    Sur la table désirée, je parviens à obtenir les totaux, par commune pour le nb et la surface moyenne des logements par typologie de logements. la moyenne générale du montant des transactions et le prix moyen toutes typologies de logements confondues.

    Par contre je suis incapable d'obtenir la moyenne du m² bâti par type de logement (colonne e_m2_bati_hab).

    J'ai recherché sur le forum, google etc mais n'ai rien trouvé.

    Voici ma requête telle qu'elle existe actuellement. Je vous passe mes différentes tentatives pour obtenir le résultat escompté tel qu'il apparait en pièce-jointe :

    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
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    SELECT 
     coalesce (geo_mutationtip2.commune, 'Total général'),
     
      sum(geo_mutationtip2.nbapt1pp) as nbapt1pp ,   round(avg(geo_mutationtip2.sapt1pp),2)as surf_moy1p,
     
    -- endroit où je voudrais insérer une colonne avec la moyenne de e_m2_bati_hab pour les appartements d'une pièce
    -- et bien-sûr, la même pour les types de logements suivants
     
      sum(geo_mutationtip2.nbapt2pp)as nbapt2pp,     round(avg(geo_mutationtip2.sapt2pp),2)as surf_moy2p, 
      sum(geo_mutationtip2.nbapt3pp)as nbapt3pp,  round(avg(geo_mutationtip2.sapt3pp),2) as surf_moy3p, 
      sum(geo_mutationtip2.nbapt4pp)as nbapt4pp, round(avg(geo_mutationtip2.sapt4pp),2)as surf_moy4p,
      sum(geo_mutationtip2.nbapt5pp)as nbapt5pp,    round(avg( geo_mutationtip2.sapt5pp),2)as surf_moy5p, 
      sum(geo_mutationtip2.nbmai1pp)as nbmai1pp, round(avg(geo_mutationtip2.smai1pp),2)as sur_moy1p, 
      sum(geo_mutationtip2.nbmai2pp)as nbmai2pp,    round(avg(geo_mutationtip2.smai2pp),2)as sur_moy2p, 
      sum(geo_mutationtip2.nbmai3pp)as nbmai3pp,   round(avg(geo_mutationtip2.smai3pp),2)as sur_moy3p, 
      sum(geo_mutationtip2.nbmai4pp)as nbmai4pp,  round(avg(geo_mutationtip2.smai4pp),2)as sur_moy4p, 
      sum(geo_mutationtip2.nbmai5pp)as nbmai5pp,  round(avg(geo_mutationtip2.smai5pp),2) as sur_moy5p, 
     
        round(avg(geo_mutationtip2.e_m2_bati_hab):: numeric,2) as prix_m2hab,   
        round(avg(geo_mutationtip2.valeurfonc),2) as moy_prixha
    FROM 
     
      dvf2018.geo_mutationtip2
     
     where e_m2_bati_hab is not null -- ceci me permet de ne sélectionner que les biens bâtis et dédiés à l'habitat
     
      group by rollup  
     
       (geo_mutationtip2.commune)
     
     
    ORDER BY
      geo_mutationtip2.commune ASC;
    Je suis sur postrgresql 9.5 avec PGADMIN III sur une base locale et bien-sûr, je suis un grand débutant en sql et tout nouveau sur ce forum.

    Désolé si je ne respecte pas les protocoles :-)
    Fichiers attachés Fichiers attachés

  2. #2
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Bonjour,

    Quelle est la règle à appliquer pour calculer "la moyenne du m² bâti par type de logement "
    - Il manque la description des tables en entrée, vous ne communiquez que la table résultante
    - Quelle colonne de quelle table donne le type du logement
    - Quelle colonne de quelle table donne la surface du logement
    - Y a -t- il des filtres à appliquer (types à ne pas prendre en compte par exemple)

  3. #3
    Nouveau Candidat au Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    novembre 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Lot (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : novembre 2017
    Messages : 7
    Points : 1
    Points
    1

    Par défaut

    Bonjour, et merci pour votre retour.

    Quelle est la règle à appliquer pour calculer "la moyenne du m² bâti par type de logement "
    la colonne à prendre en compte est e_m2_bati_hab

    - Il manque la description des tables en entrée, vous ne communiquez que la table résultante
    Dans la pièce jointe, le 1er tableau correspond à la table sur laquelle j'effectue les requêtes
    Le second tableau correspond à ce que je souhaiterai obtenir.

    - Quelle colonne de quelle table donne le type du logement
    les colonnes définissant le type de logement sont nbapt1pp (qui signifie nombre d'appartements 1 pièce vendus à l'occasion de la mutation) de 1pp (1 pièce) à 5pp (pièces).
    Pour les maison, la colonne se nomme nbmaixpp où x représente le nombre de pièces,
    - Quelle colonne de quelle table donne la surface du logement
    pour les surfaces, le nom de colonne reprend la même logique avec un préfixe "s" à la place de "nb" sapt1pp, smai1pp...

    - Y a -t- il des filtres à appliquer (types à ne pas prendre en compte par exemple)
    oui, et c'est justement là où je coince pour obtenir la moyenne de e_m2_bati_hab pour les lignes où la valeur de la colonne nbapt1pp n'est pas nulle. (et ensuite pour la colonne nbapt2pp à nbapt5pp, et nbmai1pp à 5pp)
    J'ai tenté de mettre le where dans la fonction avg() mais au lieu de me renvoyer une moyenne de e_m2_bati_hab par commune, il me renvoie pour chaque commune la même valeur qui correspond à la moyenne toutes communes confondues.

    Merci en tous cas de prendre la peine de vous intéresser à la demande,

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 334
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 334
    Points : 40 140
    Points
    40 140
    Billets dans le blog
    1

    Par défaut

    La formule générale de cette moyenne est :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SUM(prix de vente) / SUM()
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    Nouveau Candidat au Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    novembre 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Lot (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : novembre 2017
    Messages : 7
    Points : 1
    Points
    1

    Par défaut

    Bonjour SQLpro,

    Il s'agit de l'une des solutions que j'avais tentées, mais je n'obtiens pas le résultat escompté.

    J'ai intégré cette ligne en ligne 4 de ma requête de départ :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
     
     (select sum (valeurfonc)/sum (sapt1pp) from dvf2018.geo_mutationtip2 where nbapt1pp is not null )as prixm2a1pp,
    Le problème, c'est que j'obtiens un résultat qui correspond à la moyenne générale pour l'ensemble des communes mais pas ventilé par commune.
    Si je tente de rajouter un group by après le where, j'ai logiquement un message d'erreur : plus d'une ligne renvoyée par une sous-requête utilisée comme une expression.

    J'ai testé sur une table filtrée où je n'ai que les lignes où la valeur de la colonne nbapt1pp n'est pas nulle...la requête fonctionne comme je le souhaiterais, avec la moyenne par commune.


    Je SECHE...

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 334
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 334
    Points : 40 140
    Points
    40 140
    Billets dans le blog
    1

    Par défaut

    Utilisez une fonction de fenêtrage :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select sum (valeurfonc) OVER (PARTITION BY commune_id) /sum (sapt1pp) OVER (PARTITION BY commune_id)
    from dvf2018.geo_mutationtip2 
    where nbapt1pp is not null
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  7. #7
    Nouveau Candidat au Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    novembre 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Lot (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : novembre 2017
    Messages : 7
    Points : 1
    Points
    1

    Par défaut

    Bonjour SQLpro et merci pour votre retour,
    Merci également pour la fonction de fenêtrage que j'avais bien des difficultés à comprendre en consultant la documentation de postgresql et que j’appréhende beaucoup mieux appliquée à un cas concret.

    La requête fonctionne bien si je l'exécute seule, mais si je l’intègre en tant que sous-requête :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT 
    coalesce (geo_mutationtip2.commune, 'Total général'),
    sum(geo_mutationtip2.nbapt1pp) as nbapt1pp ,   round(avg(geo_mutationtip2.sapt1pp),2)as surf_moy1p,
    (select distinct  sum (valeurfonc) over (partition by commune)/sum (sapt1pp) over (partition by commune) from dvf2018.geo_mutationtip2 where nbapt1pp is not null) -- j'ai ajouté le distinct pour n'obtenir qu'une ligne par commune-- 
    FROM 
     
    dvf2018.geo_mutationtip2
     
    group by  ou group by rollup pour avoir une ligne de totaux
     
    (geo_mutationtip2.commune)
    J'obtiens toujours le message d'erreur :

    ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression
    ********** Erreur **********

    ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression
    État SQL :21000
    Pensez-vous qu'il me reste un espoir d'obtenir le résultat escompté ?

  8. #8
    Nouveau Candidat au Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    novembre 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Lot (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : novembre 2017
    Messages : 7
    Points : 1
    Points
    1

    Par défaut Big up

    Bonjour,

    Je m'engage à créer un poème pour celui qui parviendra à me sortir de l'impasse.

    Pour vous garantir la qualité qui peut en être attendue, sachez que j'ai eu 19/20 en CM1 sur un exercice de composition poétique.

    Je sens que les contributeurs vont se battre pour éclairer ma lanterne.

    Plus sérieusement, s'il vous faut des informations ou pièces-jointes complémentaires pour répondre à mon problème, je serai très heureux de vous les fournir.

    Vous remerciant par avance,

    Tristavirs

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 334
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 334
    Points : 40 140
    Points
    40 140
    Billets dans le blog
    1

    Par défaut

    Pourqui mettre cela dans une sous requête ? C'est stupide !

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT geo_mutationtip2.commune,
           sum(geo_mutationtip2.nbapt1pp) as nbapt1pp ,   
    	   round(avg(geo_mutationtip2.sapt1pp),2) as surf_moy1p,
           sum(valeurfonc) over (partition by commune) / sum(sapt1pp) over (partition by commune) AS moyenne
    FROM   dvf2018.geo_mutationtip2
    GROUP  BY geo_mutationtip2.commune
    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  10. #10
    Nouveau Candidat au Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    novembre 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Lot (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : novembre 2017
    Messages : 7
    Points : 1
    Points
    1

    Par défaut

    Je viens de lancer la requête telle que proposée :
    PG m'impose d'ajouter dans le group by "valeurfonc" et "sapt1pp"
    Lorsque je les intègre, la requête me renvoie 178 lignes.
    L'agrégation par commune ne fonctionne pas et j'ai tenté de comprendre le résultat de la requête

    Après vérification, je m'aperçois qu'il me crée une ligne pour "1 transaction a porté sur la vente de 1 appartement de 1 pièce de 9 m² cédé à un prix moyen de 13000 € par m²", et décline comme cela les transactions en fonction du nombre d'appartements 1 pièce vendus lors de la transaction et de leur surface avec par contre la même moyenne des prix au m² est reportée sur chaque ligne .

    Par ailleurs, sum(valeurfonc) sans discriminant "where nbapt1pp is not null" m'opère les calculs sur la totalité des valeurs foncières de la table, tous types de biens confondus, ce qui forcément fausse les calculs puisque je divise par la surface des seuls appartements 1 pièce. C'est pour intégrer le discriminent que j'avais opté pour la sous-requête...apparemment, ce n'était pas bien malin :-)

    Voici un extrait du résultat de la requête proposée avec les group by imposés "valeurfonc" et "sapt1pp":

    commune nbapt1pp surf_moy1p moyenne
    ARCAMBAL None None None

    BOISSIERES None None None
    BOUZIES None None None
    CABRERETS None None None

    CAHORS 1 9.00 13060.1797806753594116
    CAHORS 1 12.00 13060.1797806753594116
    CAHORS 1 13.00 13060.1797806753594116
    [...]

    CAHORS 2 2.00 13060.1797806753594116
    CAHORS 2 17.00 13060.1797806753594116
    CAHORS 2 18.00 13060.1797806753594116
    [...]
    CAHORS 3 17.00 13060.1797806753594116
    CAHORS 3 18.00 13060.1797806753594116
    [...]
    [...]
    CAHORS 6 174.00 13060.1797806753594116 -- je n'ai qu'une seule transaction sur cette commune où 6 apparts 1 pièce ont été vendus en une seule fois, pareil pour les lignes suivantes --
    CAHORS 7 126.00 13060.1797806753594116
    CAHORS 9 202.00 13060.1797806753594116
    CAHORS 10 180.00 13060.1797806753594116
    CAHORS 14 252.00 13060.1797806753594116
    CAHORS None None 13060.1797806753594116
    [...)
    CATUS 1 20.00 237358.550000000000
    CATUS None None 237358.550000000000
    [...)
    VERS 2 96.00 34752.292812500000
    VERS None None 34752.292812500000
    Merci en tous cas de tenter de me dépatouiller malgré mon manque de bases

    Forest Gump

  11. #11
    Nouveau Candidat au Club
    Homme Profil pro
    Urbaniste
    Inscrit en
    novembre 2017
    Messages
    7
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Lot (Midi Pyrénées)

    Informations professionnelles :
    Activité : Urbaniste
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : novembre 2017
    Messages : 7
    Points : 1
    Points
    1

    Par défaut Nouvel appel

    Bonjour,

    Je me permets de relancer mon appel à contribution pour obtenir des statistiques sur la table que j'interroge.

    Je mets la table requêtée en pièce-jointe, au format xlsx

    Le résultat souhaité est le suivant :

    __________somme nbapt1pp| surface moyenne sapt1pp | montant moyen e_m2_bâti apt1pp| somme nbapt2pp | surface moyenne sapt2pp | montant moyen e_m2_bati apt2pp...
    commune 1--------13----------------------21------------------------430---------------------------------------9----------------------25----------------------------420
    commune 2---------0-----------------------0--------------------------0----------------------------------------5----------------------23----------------------------320
    commune 3 ...
    ...
    Pour les sommes nbaptxpp (colonnes 1 et 4) et les moyennes de surfaces (colonnes 2 et 5), je n'ai pas de problème.

    Par contre, pour obtenir les moyennes de e_m2_bati en colonnes 3 et 6 ci-dessus, qui correspondent à la moyenne du prix au m² des biens vendus, je ne parviens pas à insérer la commande dans ma requête.

    L'idée est la suivante, par exemple pour la colonne 3 : select avg(t1.e_m2_bati) from t1 where nbapt1pp is not null group by commune

    En exécutant cette seule requête, j'obtiens le résultat escompté. Mais si je veux la mettre à la suite de sum(nbapt1pp), avg(sapt1pp), pour obtenir le résultat souhaité ci-dessus, je n'y parviens pas.

    SQLPRO en personne m'a fourni des pistes (messages ci-dessous), mais je ne suis parvenu à en mettre aucune en œuvre.

    Espérant que ma demande sera plus facilement compréhensible avec la table en pièce-jointe,

    Vous remerciant par avance,

    Tristavirs
    Fichiers attachés Fichiers attachés

Discussions similaires

  1. Débutant XML
    Par viny dans le forum XML/XSL et SOAP
    Réponses: 8
    Dernier message: 25/07/2002, 13h07
  2. [Kylix] Re Re: débutant sur Kylix et Linux.....
    Par Eclypse dans le forum EDI
    Réponses: 2
    Dernier message: 08/06/2002, 23h53
  3. [Kylix] Le débutant en Kylix et Linux....
    Par Eclypse dans le forum EDI
    Réponses: 2
    Dernier message: 08/05/2002, 11h37
  4. Réponses: 3
    Dernier message: 07/05/2002, 17h06
  5. [HyperFile] 2 questions de débutant
    Par khan dans le forum HyperFileSQL
    Réponses: 2
    Dernier message: 30/04/2002, 00h18

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