8 Replies Latest reply: Dec 8, 2015 10:48 AM by lakwinder singh RSS

    Group by not working

    lakwinder singh

      Hi Guys,

       

      Any ideas why my Group By not working?

       

      [TEST TABLE]:

      LOAD

          Value,

          sum(Value_PQ) as [Total Value],

          DISTRICT

          RESIDENT temp_SALES

         GROUP BY DISTRICT;

        

         DROP TABLE temp_SALES_RXI_WS_MONTH;

        • Re: Group by not working
          Sunny Talwar

          Either add Value in your Group By Statement or remove it from the LOAD

           

          1)

           

          [TEST TABLE]:

          LOAD

              Value,

              sum(Value_PQ) as [Total Value],

              DISTRICT

              RESIDENT temp_SALES

             GROUP BY DISTRICT, Value;

           

             DROP TABLE temp_SALES_RXI_WS_MONTH;

           

          2)

           

          [TEST TABLE]:

          LOAD

              sum(Value_PQ) as [Total Value],

              DISTRICT

              RESIDENT temp_SALES

             GROUP BY DISTRICT;

           

             DROP TABLE temp_SALES_RXI_WS_MONTH;

          • Re: Group by not working
            Marcus Sommer

            You have another field Value within your load but not in the group by - either you put it into the group by or you removed it (I think the last will be what you wants).

             

            - Marcus

            • Re: Group by not working
              Sangram Reddy

              Hi Lak,

               

              All the fields not in the group by should be aggregated. this is the cause of the issue.

               

              Instead try this:

              [TEST TABLE]:

              LOAD

                  Only(Value),

                  sum(Value_PQ) as [Total Value],

                  DISTRICT

                  RESIDENT temp_SALES

                 GROUP BY DISTRICT;

               

                 DROP TABLE temp_SALES_RXI_WS_MONTH;

              • Re: Group by not working
                Massimo Grossi

                because you miss the Value field in the group by

                 

                [TEST TABLE]:

                LOAD

                    Value,

                    sum(Value_PQ) as [Total Value],

                    DISTRICT

                    RESIDENT temp_SALES

                  //GROUP BY DISTRICT;                                     // NO

                GROUP BY Value, DISTRICT;                            // YES



                or an aggr function for the field value


                [TEST TABLE]:

                LOAD

                    // Value,                                                                // NO

                     sum(Value),                                                       // YES

                    sum(Value_PQ) as [Total Value],

                    DISTRICT

                    RESIDENT temp_SALES

                GROUP BY DISTRICT;                           




                like this

                load

                    a, b,

                    sum(c),

                    count(d)

                resident table

                where ...

                group by

                    a, b;

                • Re: Group by not working
                  lakwinder singh

                  Thanks that was a nice easy one.