2 Replies Latest reply: Mar 7, 2013 6:43 AM by kinielv2011 RSS

    sum or group by

      I have values in a column in a table QTE and I want to display the values as they present themselves in the column. When I use the values SUM (QTY) are not correct. These data are from a SQL SERVER. and I thought I should do a group by but I can not do

      Carburant_Analyse:


      LOAD

      Application as APPLICATION,

      "TYPE_PRODUIT",

      ANALYTIQUE,

      left(ANALYTIQUE,7) as COMPTE_ANALYTIQUE,

      "Type_Vehicule" as TYPE_VEHICULE,

      ANNEE,

      Mois,

      "NUM_AP5",

      QTE,  

      MONTANT,

      Compteur,

      "CONSO_MOYENNE",

      "NBRE_PRISE",

      DATE,

      anomalie,

      TypeCompteur,

      if(Application='TOTAL',QTE) as QTE_TOTAL,

      if(Application='LVNET',QTE) as QTE_LVNET,

      if(Application='LVNET',Compteur) as COMPTEUR_LVNET,

      if(Application='LVNET',"CONSO_MOYENNE") as CONSO_MOY_LVNET,

      if(Application='LVNET',"NBRE_PRISE") as NB_PRISE_LVNET,

      if(Application='GESPARC',QTE) as QTE_GESPARC,

      if(Application='GESPARC',Compteur) as COMPTEUR_GESPARC,

      if(Application='GESPARC',"CONSO_MOYENNE") as CONSO_MOY_GESPARC,

      if(Application='GESPARC',"NBRE_PRISE") as NB_PRISE_GESPARC,

      if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',QTE) as QTE_SOMILOG,

      if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',Compteur) as COMPTEUR_SOMILOG,

      if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',"CONSO_MOYENNE") as CONSO_MOY_SOMILOG,

      if(Application='Comptabilité' and TYPE_PRODUIT='Carburants',"NBRE_PRISE") as NB_PRISE_SOMILOG;


      SQL SELECT * FROM "Reporting_SucafCi".dbo."CARBURANT_ANALYSE";

       

      In simple tables I was forced to sum distinct but results are accurate when there is a single value but false when there are duplicate values

       

      I need help

        • Re: sum or group by

          LOAD

          Application as APPLICATION,

          "TYPE_PRODUIT",

          ANALYTIQUE,
          "Type_Vehicule" as TYPE_VEHICULE,

          ANNEE,

          Mois,

          "NUM_AP5",

          QTE,  

          MONTANT,

          Compteur,

          "CONSO_MOYENNE",

          "NBRE_PRISE",

          DATE,

          anomalie,

          TypeCompteur

           

          Group By 

           

          Application,

          "TYPE_PRODUIT",

          ANALYTIQUE,

          "Type_Vehicule",

          ANNEE,

          Mois,

          "NUM_AP5",

          QTE,  

          MONTANT,

          Compteur,

          "CONSO_MOYENNE",

          "NBRE_PRISE",

          DATE,

          anomalie,

          TypeCompteur ;

          SQL

          SELECT * FROM "Reporting_SucafCi".dbo."CARBURANT_ANALYSE";

           

          when i reload i have this message

          Aggregation expressions required by GROUP BY clause

          • Re: sum or group by

            LOAD

            Application as APPLICATION,

            "TYPE_PRODUIT",

            ANALYTIQUE,
            "Type_Vehicule" as TYPE_VEHICULE,

            ANNEE,

            Mois,

            "NUM_AP5",

            QTE,  

            MONTANT,

            Compteur,

            "CONSO_MOYENNE",

            "NBRE_PRISE",

            DATE,

            anomalie,

            TypeCompteur

             

            Group By 

             

            Application,

            "TYPE_PRODUIT",

            ANALYTIQUE,

            "Type_Vehicule",

            ANNEE,

            Mois,

            "NUM_AP5",

            QTE,  

            MONTANT,

            Compteur,

            "CONSO_MOYENNE",

            "NBRE_PRISE",

            DATE,

            anomalie,

            TypeCompteur ;

            SQL

            SELECT * FROM "Reporting_SucafCi".dbo."CARBURANT_ANALYSE";

             

            when i reload i have this message

            Aggregation expressions required by GROUP BY clausev