# Group by multiple columns to perform a calculation **URL:** \ **Category:** Ask for Help\ **Created:** [February 23, 2023, 11:02am UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364 "2023-02-23T11:02:37Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 11:02am UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/1 "2023-02-23T11:02:37Z")
How do I group by multiple columns to perform a calculation? For example, in the table below I want to determine the attempt score by combining columns and then finding the mean. My approach is to group the scores by competitor id, judge id, heat id, and attempt id… something like below: - competitor 1, judge 1, heat 1, attempt 1 (1-1-1-1) would be 5 - competitor 1, judge 2, heat 1, attempt 1 (1-2-1-1) would be 6 - competitor 1, heat 1, attempt 1 score would be 5.5 (for simplicity, I’m not using the actual row ids, just simple numbers in their place) | 🔒 Row ID | Score | Competitor Id | Judge Id | Heat Id | Attempt Id | | --- | --- | --- | --- | --- | --- | | 1 | 5 | 1 | 1 | 1 | 1 | | 2 | 4 | 1 | 1 | 1 | 2 | | 3 | 5 | 1 | 1 | 1 | 3 | | 4 | 6 | 1 | 2 | 1 | 1 | | 5 | 5 | 1 | 2 | 1 | 2 | | 6 | 6 | 1 | 2 | 1 | 3 | | 7 | 8 | 2 | 1 | 1 | 1 | | 8 | 8 | 2 | 1 | 1 | 2 | | 9 | 6 | 2 | 1 | 1 | 3 | | 10 | 6 | 2 | 2 | 1 | 4 | | 11 | 7 | 2 | 2 | 1 | 1 | | 12 | 8 | 2 | 2 | 1 | 2 | | 13 | 5 | 2 | 2 | 1 | 3 | | 14 | 7 | 1 | 1 | 1 | 4 | | 15 | 1 | 1 | 1 | 2 | 1 | | 16 | 7 | 1 | 1 | 2 | 2 | | 17 | 2 | 1 | 2 | 2 | 1 | | 18 | 8 | 1 | 2 | 2 | 2 | | 19 | 8 | 2 | 1 | 2 | 1 | | 20 | 2 | 2 | 1 | 2 | 2 | | 21 | 6 | 2 | 1 | 2 | 3 | | 22 | 8 | 2 | 2 | 2 | 1 | | 23 | 1 | 2 | 2 | 2 | 2 | | 24 | 5 | 2 | 2 | 2 | 3 | I’m just not sure how to do the grouping using glide. Perhaps I need to do that outside of glide. ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 11:12am UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/2 "2023-02-23T11:12:46Z")
Not sure if I’m headed in the right direction, just sharing what I’ve come across so far. It seems that the solution may be affected by where I store the data. I’m leaning towards this perspective based on how others solved similar problems using Google sheets see: [Tutorial - QUERY: "The most powerful function" in Google Sheets (part 1) - #4 by ThinhDinh](https://community.glideapps.com/t/tutorial-query-the-most-powerful-function-in-google-sheets-part-1/10432/4) Maybe something similar can be done with airtable. I’m not sure if I can pull this off using glide tables. If I hit a deadend, I’ll switch my backend to support the use case. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 23, 2023, 12:15pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/3 "2023-02-23T12:15:43Z")
> [@pigeonflight](#): > > My approach is to group the scores by competitor id, judge id, heat id, and attempt id… something like below: > > - competitor 1, judge 1, heat 1, attempt 1 (1-1-1-1) would be 5 > - competitor 1, judge 2, heat 1, attempt 1 (1-2-1-1) would be 6 > - competitor 1, heat 1, attempt 1 score would be 5.5 If I’m understanding correctly, what you actually want to do is take the average score from all judges for each combination of competitor/heat/attempt - correct? Assuming that’s correct, this is quite simple to do in Glide Tables: - Create a template column that combines CompetitorID, HeatID & AttemptID - Create a multiple relation column that matches the template column with itself - Create a rollup column, target it at the relation column, and select the average Score ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 12:21pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/4 "2023-02-23T12:21:17Z")
> [@Darren\_Murphy](#): > > - Create a multiple relation column that matches the template column with itself Thanks @Darren_Murphy… that’s about right. You’re confirming that the gap for me was the concept of a multiple relation column. I literally just saw it being used in this video [https://youtu.be/YwZF7VZIDkY?t=461](https://youtu.be/YwZF7VZIDkY?t=461) Will be exploring this direction. ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 12:29pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/5 "2023-02-23T12:29:23Z")
> [@Darren\_Murphy](#): > > matches the template column with itself What do you mean by “matches … with itself”? ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 12:33pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/6 "2023-02-23T12:33:24Z")
This may be for another thread. I don’t actually have an attempt id yet… Each judge scores each attempt in order they occur. I figure if I know the sheet order I can derive the attempt number. So this is closer to my actual table: | 🔒 Row ID | Score | Competitor Id | Judge Id | Heat Id | | --- | --- | --- | --- | --- | | 1 | 5 | 1 | 1 | 1 | | 2 | 4 | 1 | 1 | 1 | | 3 | 5 | 1 | 1 | 1 | | 4 | 6 | 1 | 2 | 1 | | 5 | 5 | 1 | 2 | 1 | | 6 | 6 | 1 | 2 | 1 | | 7 | 8 | 2 | 1 | 1 | | 8 | 8 | 2 | 1 | 1 | | 9 | 6 | 2 | 1 | 1 | | 10 | 6 | 2 | 2 | 1 | | 11 | 7 | 2 | 2 | 1 | | 12 | 8 | 2 | 2 | 1 | | 13 | 5 | 2 | 2 | 1 | | 14 | 7 | 1 | 1 | 1 | | 15 | 1 | 1 | 1 | 2 | | 16 | 7 | 1 | 1 | 2 | | 17 | 2 | 1 | 2 | 2 | | 18 | 8 | 1 | 2 | 2 | | 19 | 8 | 2 | 1 | 2 | | 20 | 2 | 2 | 1 | 2 | | 21 | 6 | 2 | 1 | 2 | | 22 | 8 | 2 | 2 | 2 | | 23 | 1 | 2 | 2 | 2 | | 24 | 5 | 2 | 2 | 2 | ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 23, 2023, 12:34pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/7 "2023-02-23T12:34:17Z")
When you create a multiple relation column, you’re asked for two columns, “Relate from…” and “Relate to…” Chose the same column for both. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 23, 2023, 12:36pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/8 "2023-02-23T12:36:59Z")
> [@pigeonflight](#): > > Each judge scores each attempt in order they occur. I figure if I know the sheet order I can derive the attempt number. The order of the rows is irrelevant for the purpose of deriving the average. Okay, so given your updated structure, the template column should combine CompetitorID & HeatID. Although, that will give you the average score of all attempts for each competitor for each heat across all judges. Is that what you want? ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 12:59pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/9 "2023-02-23T12:59:19Z")
> [@Darren\_Murphy](#): > > Although, that will give you the average score of all attempts for each competitor for each heat across all judges. Is that what you want? In any given heat I’m aiming for: 1. The `attempt score` - this is the average score per attempt (ie. the average from each judge’s score on an attempt) 2. The two highest `attempt scores` per competitor (was planning to look into this out later, but it is relevant to the current discussion) 3. The `final score` is determined from a competitors two highest `attempt scores` I’m realising that leaving out criterion #2 (ie. only the two highest scores are used to determine the final score) has an effect on the approach. This may vary between event types but first implementation uses this as the default mechanism. For this reason the order in which the attempts were recorded becomes important. The order in which scores are entered is the mechanism I have at the moment to know which attempt a judge was scoring (this may need to be reviewed in the future). Each judge must score the same attempt. An attempt score is only authentic if it is averaged against the same competitor, heat and attempt. Currently attempt order is based on the order in which each judge scored an attempt. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 23, 2023, 1:31pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/10 "2023-02-23T13:31:06Z")
Okay. It’s all very doable, and not that difficult. But it does sound to me like you’d be much better off with a column for AttemptID, rather than relying on sheet order. Apart from complicating things somewhat in terms of calculating what you need, it also sounds like it may not be a reliable method of determining attempt numbers. > [@pigeonflight](#): > > The attempt scores - these are the average score per attempt (ie. the average from the judges) I think we’ve already dealt with this, yes? > [@pigeonflight](#): > > The two highest attempt scores per competitor (was planning to look into this out later, but it is relevant to the current discussion) Several different ways you could approach this. Once you have the multiple relation, you could use a lookup column to get an array of the scores through the relation, sort them using the array sort plugin, and then either use an array slice column to pick out the last two - the sort will return in ascending order - or (perhaps better) two separate single value columns. ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 2:12pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/11 "2023-02-23T14:12:06Z")
Thank you very much @Darren_Murphy. I’m jumping back to my “real work” for the rest of the day. Hopefully I can take a look and try out some of your suggestions later. ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 23, 2023, 2:13pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/12 "2023-02-23T14:13:50Z")
> [@Darren\_Murphy](#): > > you’d be much better off with a column for AttemptID I tend to agree. I’ll look at the best way to capture this… I need a way that each judge’s entry is tied to the correct attempt id every time. I don’t fully trust the idea of order in sheet. ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 24, 2023, 11:22am UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/13 "2023-02-24T11:22:45Z")
> [@Darren\_Murphy](#): > > this is quite simple to do in Glide Tables: > > - Create a template column that combines CompetitorID, HeatID & AttemptID > - Create a multiple relation column that matches the template column with itself > - Create a rollup column, target it at the relation column, and select the average Score Thanks @Darren_Murphy… I’ve added a simple way for judges to specify attempt id. With that in place I did the… **combined-field-values template \> rel \> rollup calculation dance** (my name for this) and it works as expected. I now have a working prototype. Not 100% happy with the UI but I’ll review and see how best to improve that. ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 24, 2023, 11:29am UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/14 "2023-02-24T11:29:29Z")
I’m still curious about “auto-deriving” the attempt id but will leave that out for now. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [February 24, 2023, 11:57am UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/15 "2023-02-24T11:57:18Z")
Do you mean auto-deriving based on sheet order? If yes, that’s easy - do a lookup through a relation thats filtered using competitor+judge+heat. This will give you an array of the 3 attempts, where item 1 is attempt 1, item 2 is attempt 2 and item 3 is attempt 3. You can then extract individual items from the array using single value columns. ---
**Author:** ![pigeonflight](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/pigeonflight/32/54611_2.png) [@pigeonflight](https://community.glideapps.com/u/pigeonflight)\ **Post date:** [February 24, 2023, 12:12pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/16 "2023-02-24T12:12:45Z")
I’ll leave it out for now, just to avoid the edge case of judges who enter scores in the wrong order. I think it’s an extreme edge case but would rather discuss it with the users before implementing auto-derived attempt ids. ---
**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\ **Post date:** [February 25, 2023, 12:13pm UTC](https://community.glideapps.com/t/group-by-multiple-columns-to-perform-a-calculation/58364/17 "2023-02-25T12:13:32Z")
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.