Is there a way to group by without using a selected column?

[that_anonymous_guy]

Hello everyone, I want to get the value like this

Item	Child Item	Quantity
1	01	5
1	01	5
1	02	6
1	02	6
1	03	8
1	03	8
1	04	9

I want to have the total quantity item wise i.e Item-1 should have quantity as 28. 

This is what I am doing which is giving me the total sum. 

Table1:

load Item, Child Item, Quantity and some other 27 columns from src; //It has around 30 columns

left join

Table2:

load distinct Item,sum(Quantity)

resident Table1

group by Item;

I want to join sum(quantity) field to the table but the sum should consider only distinct Child items else it would give the total sum. To get that I assume I would have to load ChildItem as well and then group by using ChildItem in addition to parent item but  If I load Child Item as well in table 2 then that will also get joined which would be wrong.

Is there a way around to get this done?

[MayilVahanan · Accepted solution]

Hi

Try like below

Temp2:
LOAD *, Item & [Child Item] as %key INLINE [
Item, Child Item, Quantity
1, 01, 5
1, 01, 5
1, 02, 6
1, 02, 6
1, 03, 8
1, 03, 8
1, 04, 9
];

Final:

Load distinct %key, Quantity as DistinctQuantity Resident Temp2;

Use DistinctQuantity  in Expression. Hope It will help you.