Group By Question

TMonty0319
TMonty0319 Member
in Connectors

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

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You have two options.

    1. 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
    2. 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.
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You have two options.

    1. 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
    2. 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.
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • TMonty0319
    TMonty0319 Member

    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
    TMonty0319 Member

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

Categories