Group by with where clause

[#1]

Hello,

Is there a way to use group by the same time as where clause?

I'd like to group by field1, resident Table1, but where field2 is 1, and field3 is 4.

[Gysbert_Wassenaar]

Yes, you can use both a WHERE and an GROUP BY clause in the same load statement.

LOAD A, B, sum(C) as C

FROM ABC

WHERE C > 0

GROUP BY A, B

;

[#3]

I am doing this using a load resident, where can I put the where clause?

NewTable:

Load

     field1, 

     Sum() as [SumC]  

     Resident [Table1]

Group by

     field1;

Now where to add below?

where field2=1 and field3=4

[Gysbert_Wassenaar]

NewTable:

Load

     field1,

     Sum() as [SumC] 

Resident

  [Table1]

Where

  field2=1 and field3=4

Group by

     field1

  ;

[sunny_talwar · Accepted solution]

May be this:

NewTable:

Load

    field1,

    Sum() as [SumC] 

Resident [Table1]

Where field2 = 1 and field3 = 4

Group by field1;

[marcus_sommer]

If you are dealing with large datasets you should better separate where and group by in two loadings - it will be much faster to filter at first the data and then to aggregate them.

- Marcus