[Tutorial] Calculations in databases (quick primer)

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Calculations in databases (quick primer)

Postby Villeroy » Sat Jan 09, 2010 3:26 pm

Hoizontal calculation in rows (Volume=Height*Width*Lenght): calculate_form.odb
 Edit: This file has been replaced and is now a link at the bottom of this posting 

Vertical calculation in columns(aggregation sum,count,average,...): sumproduct.odb

Vertical aggregations can be used together with grouping in order to get the function results for each combinaition of groups.
Example: In the second example file you see various function results for the selected combination of "category 1" and "category 2".
Refer to any SQL documentation, such as http://www.1keydata.com/sql/sql.html (chapters about SUM, GROUP BY, HAVING
Hint: Calc's data pilot can do very similar aggregations from list ranges as well as from database tables with a flexible cross-table layout.
http://wiki.services.openoffice.org/wik ... /DataPilot

The supported set of features depends on the type of database connection as indicated in the status bar of your database window.
File based databases (dBase, spreadsheets, csv) support horizontal calculation only: /specifications of file based functions

True databases allow Base to apply the whole arsenal of Base features plus any additional functions supported by the connected database engine. Base's built-in database engine is fully documented at http://hsqldb.org/doc/guide/ch09.html
Hint: Try direct SQL and read carefully the error messages. Quite often the database engine understands your SQL better than Base does. Quite often the error messages are more comprehensible.
My [Tutorial] Structured Query Language in Base explains the different ways to use SQL with Base queries, forms and reports.
volume = height * width * length
(21.11 KiB) Downloaded 442 times
Last edited by Villeroy on Wed May 27, 2015 3:19 pm, edited 1 time in total.
User avatar
Posts: 26401
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Calculations in databases (quick primer)

Postby szd6k8 » Wed May 27, 2015 3:06 pm

This is so frustrating. I don't understand where the calculation goes. I see where they put sum, min, max, avg, etc., but they don't seem to allow select distinct. I wish they had some kind of tutorial for OoBase for dummies............LOL. The calculate_form.odb is a link that is no longer available. I've been trying to put the calculation in the new column of the query. Still getting error. Any more suggestions would be greatly appreciated.

 Edit: Calculate_form.odb has been replaced and is now a link at the bottom of the previous posting. 
OenOffice 4.1.1 Windows 8.1
Posts: 29
Joined: Wed Mar 25, 2015 7:24 pm

Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest