Page 1 of 1

[Solved] Grouping by a field and then subtotaling each group

Posted: Tue Sep 05, 2017 12:03 am
by manderson
Hi all,

I'm a bit of a novice at this kind of thing, so sorry if there is a really obvious solution, but i'd appreciate any help.

I currently have a database with 4 fields (Date, Expense, Cost and Code). The 'Code' field is where I input a category for the item I'm putting into the database (e.g if I was logging a train ticket I would use the code 'TT'. I would like to create a report which groups all of the items with the same code together, then shows a subtotal at the bottom of each group (this would be the sum of all of the items in the 'Cost' field) I'd also then like a total to be displayed at the bottom of the report which would be a sum of everything in the 'Cost' field.

This is so that I could print a report of all of my expenses with different categories for each type of expense I'm claiming, so I could easily see how much I've spent in each category.

Thanks again for any help :D !

Re: Grouping by a field and then subtotaling each group

Posted: Tue Sep 05, 2017 3:31 pm
by Sliderule
manderson:

Welcome to the OpenOffice / LibreOffice Base forum.

Please go to the link below . . . to see a similar question and answer by Sliderule, including a demo database to show it can be accomplished.

If you have not already done so, please download the Base Extension, Oracle Report Builder ( ORB ).

Next, please download the sample Base database file ( *.odb ) from the link below. It demonstrates using functions within ORB to calculate sub-totals exactly as you describ. The sample will calculate totals for each country in a continent, and, also, for all countries. :bravo:

viewtopic.php?f=42&t=70217&p=313938

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Re: Grouping by a field and then subtotaling each group

Posted: Tue Sep 05, 2017 3:50 pm
by Villeroy
Create a query which groups by one or more categories and sums up the numbers. Then create a report from that query.
A query may look like this in SQL view:

Code: Select all

SELECT YEAR("Date") AS "Year", MONTH("Date") AS "Month, "Category", SUM("Value") AS "Sum"
FROM "Some Table" 
GROUP BY YEAR("Date"), MONTH("Date"), "Category"
This shows the sum of values for every combination of year, date and category.

You can also open a spreadsheet and create a pivot table directly from the database table:
Data>Pivot Tables>Create...
Use the table of your registered database
Drag the category and the date to "Row Fields".
Drag the values to "Data Fields".
[OK]
Click any date cell and call Data>Group>Group... group by year and month.
You may also layout the categories as column fields which gives a nice cross table for charts.

Re: Grouping by a field and then subtotaling each group

Posted: Tue Sep 05, 2017 4:58 pm
by manderson
Sliderule wrote:manderson:

Welcome to the OpenOffice / LibreOffice Base forum.

Please go to the link below . . . to see a similar question and answer by Sliderule, including a demo database to show it can be accomplished.

If you have not already done so, please download the Base Extension, Oracle Report Builder ( ORB ).

Next, please download the sample Base database file ( *.odb ) from the link below. It demonstrates using functions within ORB to calculate sub-totals exactly as you describ. The sample will calculate totals for each country in a continent, and, also, for all countries. :bravo:

viewtopic.php?f=42&t=70217&p=313938

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Thanks for your help! That works just the way I want it to! :D