Group By Question

TMonty0319 · 2024-03-26T10:26:35+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Grouping

Magic ETL

Accepted answers

GrantSmith

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.

All comments

GrantSmith

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

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

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