Error when using group by clause

[#1]

Can anyone tell me why i am getting the error over this query when i am trying to use group by clause in below query.. Kindly suggest me how should i modify this query in order to run it properly.

Thanks

LOAD

    SLUG,

    TIME,

    HOURS,

    Block,

    COUNT(LIH) as Sums

FROM [lib://Desktop/generate.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group BY SLUG;

ERROR'S ARE ALSO ATTACHED.

[oknotsen]

You will either have to group by slug, time, hours and block or remove time, hours and block in order for this to work.

[vvvvvvizard]

Try load *',

Count(lih) as sums

Resident tablename

Group by slug, time, hours,   block;

Change count(lih) as sum to just lih in the first table

[ArnadoSandoval · Accepted solution]

Muhammad,

All the columns appearing in your LOAD or SELECT clause should be included in your GROUP BY verb excluding the aggregated columns, like this:

LOAD

    SLUG,

    "TIME",

    "HOURS",

    Block,

    Count(LIH)      as    Sums

FROM [lib://Desktop/generate.xlsx]

(ooxml, embedded labels, table is Sheet1)

Group by SLUG, "TIME", "HOURS", Block;

HTH

[kkkumar82]

Hi Muhammad,

If you want the sum by SLUG then

Remove the group by for the Load and do a resident of it and do a group by using SLUG like below.

Data:

LOAD

    SLUG,

    "TIME",

    "HOURS",

    Block,

    Count(LIH)      as    Sums

FROM [lib://Desktop/generate.xlsx]

(ooxml, embedded labels, table is Sheet1);

Load

SLUG,

Count(LIH) as Sums

Resident Data

group by SLUG;

[#6]

Thanks for the participation.

but still getting the error when using the query.

[#7]

Thanks for the participation .. it's working !!

But one thing more i want to know that i am using group by clause not to repeat the same data in slug field but after using this query getting the repetition of data .. any suggestion ?

i dont want to repeat the same data more than one time