GroupBy and multiple Sum columns

[iSpeak]

I have a SP list "classes" with columns "subject_id", "academic_hrs" and "actual_hrs" similar to the following...

ID     subject_id    academic_hrs   actual_hrs
1       subject_1         4             3
2       subject_2         4             3
3       subject_2         4             3
4       subject_1         4             3
5       subject_2         4             3
6       subject_1         4             3
7       subject_2         4             3

I need a table that aggregates all the classes to see something like the following...

subject_id     row_count      sum_academic_hrs     sum_actual_hrs
subject_1      3                    12             9
subject_2      4                    16             12

I've been trying to do this with the GroupBy function, but can't figure out how to get both the row_count AND the summed columns. Please show me how this can be accomplished.

My attempt...

AddColumns(
    GroupBy(
        classes,
        "subject_id",
        "all_subjects"
    ),
    "row_count",
    CountRows(all_subjects)
)

[CarlosFigueira · Accepted solution]

You can add multiple columns at once in a single AddColumns call. In your case, you can have an expression like the one below:

AddColumns(
    GroupBy(
        classes,
        "subject_id",
        "all_subjects"
    ),
    "row_count",
    CountRows(all_subjects),
    "sum_academic_hrs",
    Sum(all_subjects, academic_hrs),
    "sum_actual_hrs",
    Sum(all_subjects, actual_hrs)
)

Hope this helps!

[iSpeak]

That worked perfectly. The part I was missing was adding "all_subjects" to the function...

Sum(all_subjects, academic_hrs)

Thanks for the help!

[Sulay]

Hi all!

I've tried what you suggest but althought there is no error in the formula the output for the sum is empty. 

I need a table grouped by employee ID with the sum of the duration of their contracts. 

My data source is a SP list where duration is a calculated column. 

The formula I have used in the table is:

AddColumns(
GroupBy(
'Contracts';
"Employee_ID";
"Employee"
);
"TotalDuration";
Sum(
Employee;
'Duration'
)

Hope any of you could help me! I would really appreciate it. 

Thanks!

[gumgih]

@CarlosFigueira 

Thanks for your suggestion, but can we do this in Model-Driven App.

I have 1000+ records for single resource and want to SUM of their hours.

Any kind of help will appreciate.

Regards, 

Mayank