Ask Your Question
0

How to aggregate rows using group by?

asked 2018-06-19 22:06:40 +0200

Cerin gravatar image

I have a spreadsheet with two columns, "Name" and "Score" and hundreds of rows. Each row can contain a non-unique name.

I want to create a different view, that automatically updates, which aggregates these rows and calculates the average score for each unique name. In SQL, this would be done trivially with a statement like "SELECT name, AVG(score) FROM table GROUP BY name;"

I believe Libreoffice has some similar functionality, but I'm not familiar enough with it. Would this be case for pivot tables or some sort of conditional row expressions? How would I do this in Libreoffice?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-06-22 20:58:00 +0200

hjek gravatar image

A simple but possibly insufficient solution is to use a formula. If we were to assume that you already had a list of unique names in the A column, you could fill another column with this:

=AVERAGEIFS(score,name,"="&$A1)

If we need to create the list of unique names however, we're going to have to use a pivot table (or macros; or a really hacky formula solution).

Select the two columns, click Insert -> Pivot Table... -> Current Selection -> OK. Drag Name over in the Row Fields list, and drag Score over in the Data Fields list. Double-click on Sum - Score in the Data Fields list and select Average from the Function dialog.

(If this works for you, please click on the ✓ to accept the answer.)

edit flag offensive delete link more
0

answered 2018-06-22 23:58:33 +0200

Lupp gravatar image

updated 2018-06-23 00:50:53 +0200

Use a pivot table (formerly 'DataPilotTable') with your data range as the source, the Name-column as the (only) row field and the Scoure column as the only data field with evaluating function AVERAGE.

You may add a filter and define the output to start on the same sheet if you want.

The output will not refresh automatically, but on 'Refresh' chosen from the context menu of any cell used for it.
A slightly annoying fact is (as I see it) that formats once set for the output range are ignored by the refresh.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-19 22:06:40 +0200

Seen: 2,388 times

Last updated: Jun 23 '18