
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Group by with where clause
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this:
NewTable:
Load
field1,
Sum(
Resident [Table1]
Where field2 = 1 and field3 = 4
Group by field1;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
;
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am doing this using a load resident, where can I put the where clause?
NewTable:
Load
field1,
Sum(
Resident [Table1]
Group by
field1;
Now where to add below?
where field2=1 and field3=4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
NewTable:
Load
field1,
Sum(
Resident
[Table1]
Where
field2=1 and field3=4
Group by
field1
;
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this:
NewTable:
Load
field1,
Sum(
Resident [Table1]
Where field2 = 1 and field3 = 4
Group by field1;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
