Group By and Sum (1 Viewer)

luzz · Dec 7, 2017

Hi all, I would like to ask if it is possible to perform group by and sum based on the image i have attach below?
As you can see from the image, I have boxed up the PO,Colour to show you all that in the same PO, there will be duplicate colour. However, the colour has difference pounds and yarns weight and the line is difference.

I use this data to create a unmatched query based on the PO and Line so i am thinking if it is possible to group all the same colour together, and sum the pounds and yarns weight? And at the same time, i can still continue to use PO and Line in unmatched query.
Thank you.

Pat Hartman · Dec 7, 2017

If you want to group and sum, you must eliminate the columns that make a row different. So to group by PO and color and sum pounds, you need to eliminate yarn weight and item number from that query.

luzz · Dec 7, 2017

Pat Hartman said:
If you want to group and sum, you must eliminate the columns that make a row different. So to group by PO and color and sum pounds, you need to eliminate yarn weight and item number from that query.

I see~ so it is impossible to perform grouping if there is a column that make them different. But what if the column is related to the field and cannot be remove?
Like for the image below; The GLA fields is tied to the colour thus cannot be remove. Does this means that i cannot perform group by in the report?

isladogs · Dec 7, 2017

luzz said:
I see~ so it is impossible to perform grouping if there is a column that make them different. But what if the column is related to the field and cannot be remove?
Like for the image below; The GLA fields is tied to the colour thus cannot be remove. Does this means that i cannot perform group by in the report?

No.

If the fields contain different values they will be grouped as separate records. So if you group by your first two fields you can then sum or count the next two field and get one record .... as long as you omit GLA from your query.

GLA is part of your table. Just don't include it in your aggregate query

If you ALSO group by GLA, you will get each record separately as the GLA values are different.

Same applies to your original example.
If you are unclear, just try it yourself

plog · Dec 7, 2017

Using your the first image you posted as starting data. What would you like the final data to be? Don't explain it to me, show me what data you expect your query to produce when you feed it the data from the initial image.

Pat Hartman · Dec 7, 2017

luzz,
The point of a summary is that details are lost. They don't disappear from the underlying data, they just don't appear in the final results.

Group By and Sum (1 Viewer)

luzz

Registered User.

Attachments

Pat Hartman

Super Moderator

luzz

Registered User.

Attachments

isladogs

MVP / VIP

plog

Banishment Pending

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread