topic Re: How to get column sum according to same column values? in Discussions

How to get column sum according to same column values?

UserID16644 — Wed, 03 Apr 2024 07:41:50 GMT

Hi all

How to calculate the Total Qty if they have the same code? I am currently using Col Sum(:Qty) but it is getting the sum of all column values. I just want the sum of values with the same code & date

Date	Code	Qty	Total Qty
10-23-23	00	4	9
10-23-23	00	5	9
10-23-23	01	6	6
10-24-23	03	3	3
10-24-23	04	4	4
10-25-23	05	6	8
10-25-23	05	2	8

Re: How to get column sum according to same column values?

Stuart_Little — Wed, 03 Apr 2024 08:35:12 GMT

You can add the Code column as a grouping variable to achieve this - The required formula is:

Col Sum (Qty , Code)

I've put this in the attached table to illustrate.

Hope that helps,

Stuart

Re: How to get column sum according to same column values?

txnelson — Wed, 03 Apr 2024 08:46:36 GMT

Here is the Scripting Index description of the Col Sum()

Note that the function allows for By Variables. Example 4 from the same Scripting Index entry, illustrates the use of the By variables with columns Age and Sex specified as By variables

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column( "Sum for Each Age and Sex Group",
	Formula( Col Sum( :height, :age, :sex ) )
);

With the results