Rollup in the Libreoffice Base parser

[manolis1]

I have the following query, that works when send directly to MariaDB, but not when send via the parser.

SELECT `ANK`.`company`, `ANK`.`journal`,`ANK`.`pb`, `ANK`.`db`, `DEUTERO`.`dbdesc`, SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) AS `T`, SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `T_prev`, SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) - SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ) AS `Diff`, ( SUM( CASE WHEN `ANK`.`year` = 2024 THEN `ANK`.`dc` END ) / NULLIF ( SUM( CASE WHEN `ANK`.`year` = 2024 - 1 THEN `ANK`.`dc` END ), 0 ) - 1 ) AS `Percent` FROM `ANK` LEFT JOIN `DEUTERO` ON `ANK`.`db` = `DEUTERO`.`db` WHERE `ANK`.`year` >= 2024 - 1 AND `company` = 'VD' AND `ANK`.`omada` = '6' AND `ANK`.`month` <= 12 AND `ANK`.`journal` = 'ΓΗ' GROUP BY `ANK`.`pb`, `ANK`.`db` WITH ROLLUP

I would like the years to be parameters, but as the ROLLUP function does not seem to be understood by the parser, what are my alternatives?

Libreoffice 25.2.5.2, MariaDB 12, Linux 6.14

[CRDF]

I think you could try with a View.

[manolis1]

Could you elaborate a bit?

[CRDF]

Sorry!
Forget it.

[Villeroy]

If this is for a report, the report can do the rollup.
If this is for a form, a “power filter” with a calculated subform can do.
When importing to a spreadsheet, the job is trivial.

[manolis1]

• It is supposed to be drawn into a libreoffice writer file, were the user is supposed to comment on deviations.
• The writer file itself is generated by egroupware and contains already some fields from the egroupware database as well as instructions.
• I prefer to avoid using the spreadsheet as an intermediate step (this is what users do now)
• The report builder seems interesting, though it would probably not be the solution in the case at hand.

[Villeroy]

@manolis1

I prefer to avoid using the spreadsheet as an intermediate step

I was thinking of a spreadsheet as the final step. I use Calc as reporting engine with database ranges that are linked to Base record sets.

@manolis1

(this is what users do now)

Let me guess: they actually store record sets in spreadsheets, which is a no-go indeed.

@manolis1

The report builder seems interesting, though it would probably not be the solution in the case at hand.

Basically, it produces a Writer document with a tabular structure.

[CRDF]

So for eventual reference the possible solution via View(s).
vBase returns the SUM(CASEs columns without parameters:

vRollup selects from vBase and must be completed as required in the question, of course:

And this query runs with the year parameter via Base GUI parser:

Dummy "Table1"

[RobertG]

For a query with ROLLUP: SELECT WITH ROLLUP | MariaDB Documentation
The function needs grouping of all elements in the query, which haven’t be defined by other functions like SUM, AVG… The query will need executing SQL directly. So you have to set this in SQL-mode of the query editor (last button on the right).
Result will be the the SUM (ore other information) rolled up for every group.

[CRDF]

But the ROLLUP can’t be defined in View vRollup?
Rem: I don’t have the elements to try this here in this probe.
Idea of course should put all columns asked for in the views, and in Base parser only
SELECT * FROM vRollup WHERE ano = :year

[RobertG]

I never use parameters. Will get all the values I need from a “filter” table. This also works in direct SQL and also in views.
.
The query @manolis1 created is only grouped by

GROUP BY `ANK`.`pb`, `ANK`.`db` WITH ROLLUP

and there are many more fields in the query which aren’t part of a group or a special function like SUM():

`ANK`.`company`, `ANK`.`journal`, `DEUTERO`.`dbdesc`

Might be there are more fields, which have also be grouped.

[Villeroy]

SELECT SUM("number")AS"Total",
  COUNT(*) AS "Records",
  MIN("date")AS "From",
  MAX("date") AS"Until"
FROM "tbl"

Total	Records	From	Until
123456,98	98767	02.01.20	13.08.25

Selects a single record of aggregated data. Aggregation boils down many records to a few records or only one.

SELECT SUM("number")AS"Total",
  COUNT(*) AS "Records",
  MIN("date")AS "From",
  MAX("date") AS"Until"
FROM "tbl"
GROUP BY "Category"

does exactly the same for each category. You get one record for each unique category.

In order to make it more obvious which category belongs to which aggregation, you would select the category as well, but the whole thing works without explicit name dropping.

The other way round, mentioning some category without aggregation nor grouping does not tell in which way the mentioned category should be boiled down, therefore any SQL database raises an error.

The ROLLUP keyword appends the single record of aggregations for the whole record set as an extra. I’m not sure if ROLLUP is part of the SQL standard. I’ve never been confronted with this keyword. IMHO, it is a nice-to-have, but not necessary. You can always perform this extra aggregation in a separate statement and merge it with the grouped statement one way or the other.

[CRDF]

Yes, ROLLUP is resource of GROUP BY, so in the SELECT, SUM( must appear, as did @manolis1.
Will append the table 2 ~ 3 random values for each year and try to create View(s) grouped by year only (without the = value).
Or return just 1 value for other columns than YEAR (?) as my tentative views do (returns just 1 row for each “ano”).
To see what results being called via Base GUI as

SELECT * FROM “vRollup” WHERE “year” = :p

(?)

[Villeroy]

@CRDF

SELECT * FROM “vRollup” WHERE “year” = :p

I confirm, that it works that way with MariaSQL connected via JDBC and ODBC. I can’t get it working with SDBC, which may be a bug or my inexperience with that database engine.

SELECT * 
FROM `dummy`.`vRollup` AS `vRollup` 
WHERE `Year` = :Year_Number

[CRDF]

Trying to avoid putting the parameter inside the SUM(CASEs I did functions for them:
SC(year)
and D(year) for the Diff.
So I think it should be something like this…

CREATE VIEW vRollup AS
SELECT ano, SC(ano) AS T, SC(ano - 1) AS T_prev, D(ano) AS Diff,
SC(ano)/NULLIF(SC(ano - 1), 0) - 1 AS Percent 
FROM Table1
GROUP BY ano, T WITH ROLLUP;

[Connected directly]