Ask Your Question
1

How can I calculate aggregate sums in Calc?

asked 2017-09-29 23:25:08 +0200

stanton gravatar image

Assume I have a table like the following:

Mammal | Cow      | 17
Mammal | Horse    |  5
Mammal | Pig      | 11
Bird   | Duck     | 12
Bird   | Goose    |  8
Fish   | Sturgeon |  3
Fish   | Carp     | 14
Fish   | Trout    | 26

I would like to calculate sums across the third column, based on the value of the first. Like:

Mammal | 33
Bird   | 20
Fish   | 40

In a SQL database I would just do

SELECT A, SUM(C) FROM table GROUP BY A;

How can I automate this in Calc? Auto-update (sum changes as soon as I change anything in the first table) would be nice, but is not mandatory.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2017-09-30 05:06:13 +0200

Jim K gravatar image

updated 2017-09-30 05:10:37 +0200

Use a Pivot Table.

To do this, insert a new first row. Enter "Type" in A1, "Animal" in B1, and "Count" in C1. Then select cells A1 through C9, go to Data -> Pivot Table -> Create, and press OK.

In the Pivot Table Layout dialog, drag "Type" to Row Fields, and "Count" to Data Fields. Then press OK.

Type            Sum - Count
~~~~            ~~~~~~~~~~~
Bird            20
Fish            43
Mammal          33
Total Result    96

To update, right-click on the pivot table and choose Refresh.

edit flag offensive delete link more

Comments

Sounds good, except that the menu item you describe is missing. Under Data > Pivot Table, I only see Refresh and Delete—the Create item is not there. LO 5.1.6.2 on Ubuntu MATE 16.04 LTS.

stanton gravatar imagestanton ( 2017-09-30 17:14:22 +0200 )edit

Got it. It’s under Insert > Pivot Table in later versions, albeit even Help still points to the old location.

stanton gravatar imagestanton ( 2017-09-30 17:15:53 +0200 )edit

My instructions were made using LO 5.4.0.3, so it would seem that the newer location is the Data menu. Alas, the menu changes frequently between releases.

Jim K gravatar imageJim K ( 2017-09-30 17:56:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-29 23:25:08 +0200

Seen: 1,521 times

Last updated: Sep 30 '17