Group by not working

[Anonymous]

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;

[sunny_talwar · Accepted solution]

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;

[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

[Anonymous]

So each field needs to go into the group by?

[reddy-s]

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;

[sunny_talwar]

Whatever isn't getting aggregated needs to be within the Group By statement

[maxgro]

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;

[Anonymous]

Thanks that was a nice easy one.

[Anonymous]

Hi

I put images on my other question as i couldn't put the file on here.  The table expression not working within a KPI.

Thanks