Group By Question

TMonty0319 · March 26

Im trying to do a group by to get a new column with total sum of charges by bill ID. I want the total sum to be constant all for every day associated with Bill ID. When I do a group by I am not getting the new column to sum the charges.

Bill ID	Date	Charge
502	4/10/2023	$85
502	4/11/2023	$76
502	4/12/2023	$110
502	4/13/2023	$78
502	4/14/2023	$79
502	4/15/2023	$115
502	4/16/2023	$101
502	4/17/2023	$94
502	4/18/2023	$77

This is result i want. Could someone please assist?

Bill ID	Date	Charge	Total for Bill ID
502	4/10/2023	$85	$815
502	4/11/2023	$76	$815
502	4/12/2023	$110	$815
502	4/13/2023	$78	$815
502	4/14/2023	$79	$815
502	4/15/2023	$115	$815
502	4/16/2023	$101	$815
502	4/17/2023	$94	$815
502	4/18/2023	$77	$815

GrantSmith · March 26

You have two options.

Keep with your group by but only group based on the Bill ID and then use a join tile to join that back to your original dataset based on the Bill ID, this will put the total on every record with that same bill ID
The other option is to utilize a Rank and Window tile which will do something similar if you utilize a Bill ID partition with a SUM aggregation.

GrantSmith · March 26

You have two options.

Keep with your group by but only group based on the Bill ID and then use a join tile to join that back to your original dataset based on the Bill ID, this will put the total on every record with that same bill ID
The other option is to utilize a Rank and Window tile which will do something similar if you utilize a Bill ID partition with a SUM aggregation.

TMonty0319 · March 26

Thanks yeah thats what was doing. for some reason in my data the number is coming back as 5.8517881E7 instead of 58,517,881. Maybe i Just need to reformat it?

TMonty0319 · March 26

All set. Just had to reformat column as Integer. Thanks

Group By Question

Best Answer

Answers

Categories