group by excel query

[emyemyemy]

Hello every one ,

I want to group by my excel query  but when I reload my code :error shows Aggregation expressions required by GROUP BY clause !

here is my code  :

LOAD   N°,
week, sum(production)

FROM
[path]
(ooxml, embedded labels, table is mysheet) group by

Month(MakeWeekDate(year(Today()),week));

;

Any help plz thank you ,

[Frank_Hartmann]

May be this:

LOAD    N°,
week,
sum(production) as Production_sum
FROM
[path]
(ooxml, embedded labels, table is mysheet) group by N°, week;

If you want to use aggregation functions like Sum() in your LOAD, you need to use a GROUP BY clause for all fields that are not embedded in aggregation functions.

[emyemyemy]

hi @Frank Hartmann    

Thank you  for your help and attention,

your suggestion is helping me  by reloading  my code successfully  ( error not shown) .

but it's not what I'm looking for : I need to group by my code with one field only  (group by  month from week field= Month(MakeWeekDate(year(Today()),week)) )

can you explain  to me  why I should write all   fields    that are not embedded in aggregation functions while it's not going to make any difference in query result  or  should I reorder  the group by clause , and second should I use aggregation function to can use group by clause  ?

[Frank_Hartmann · Accepted solution]

Group by only works if all fields which are not aggregated are within the Groupby statement. Otherwise you will get an error. Not sure if i understood your requirement correctly but give this a try:

TABLE1:
Load *, month(makeweekdate(2018,week)) as Month;
LOAD * INLINE [
    N°, week, production
    1, 22, 435
    2, 23, 354
    3, 22, 3245
    2, 23, 455
    8, 22, 324
    4, 23, 435
    5, 27, 4545
    6, 29, 324
    ];

left join


TABLE2:
Load Month, sum(production) as production1  Resident TABLE1 Group by Month; //DROP Table TABLE1;

Result will be this:

production1	Month
1244	Jun
4004	Mai
4869	Jul

[emyemyemy]

LOAD    N°,  month(makeweekdate(2018,week)) as Month ,  sum(production) as Production_sum

  FROM  [path]  (ooxml, embedded labels, table is mysheet)  ;

I got same result as you did here with out using group by  ^^ .

Thank you , your answer is brilliant  .