error message "invalid expression" while loading

[#1]

Hello,

When executing the following code:

ElectricityDaily:

LOAD Date,
 ShortDate,
 Type_ID,
 MonitoringPoint_ID,
 sum(Value_E) as Value_E_Daily,
 Unit_ID
FROM [QVDs\Data.qvd] (qvd)
GROUP BY ShortDate;

I get the error "Invalid expression"

I have no idea what the problem is...

[#2]

I think it is because Date, Type_ID, etc... are not mentioned in the group by statement.

[#3]

But I only want to sum across Date_Short... do I have to include all the fields in the group by?!?!

[prieper]

Depending, what you need to see: if the grouping fullfils your requirement, go for it. If not, you may look for First/Last Sorted Value or Min/Max String.

Peter

[Anonymous]

Yes, you will need to use group by over all fields with no aggregation, or separate the load statement.

Imagine the following:

Year Unit Sales
2008 A 5000
2008 B 8000
2009 A 6000
2009 B 5000

Now if you want to sum up the Sales and group it by Year you will end up with:

Year Sum(Sales)
2008 13000
2009 11000

This looks ok and is what we would expect. If we also added the Unit and did not group by the Unit value, what would we expect to see?

Year Unit Sum(Sales)
2008 A 13000
2008 B 13000
2009 A 11000
2009 B 11000

This is obviously wrong so you have to group by the fields that are not part of an aggregation. Or just load Date_Short and your aggregation in one table and you can either join that to the table one way or another or keep it separated and linked with Date_Short as the key field.

[Anonymous]

hey jsn

i have 30 columns in my table, so do i need to write all the 30 names in the group by clause, except the column names which i m using with my count or sum function

[Anonymous]

Hi,

That is correct. You need to specify all fields in your group by clause that are not part of an aggregation.

[robert_mika]

Karunpreet Soni

Why would you group by 30 Columns.

That would gave your SQL statement additional headache.

[MayilVahanan]

HI

Split the query like this

TableA:

Load Uniqueid, field1, field2 from table1;

join(TalbeA)

Load Uniqueid, Sum(field3) resident TableA;

or

Create the Key from table1 and by using that key, do Join operation.

Hope it helps