Grouping in Libreoffice Base query editor

osantos · February 17, 2023, 9:26pm

I am connecting to a MSSQL database through ODBC and I am tryint to execute the following query which I created using the query editor:

SELECT "CobroV"."NumRecibo", "CobroV"."Id_Cobro", SUM( "CobroVEstCobro"."Importe" ) "Pagat" FROM { oj "VFIN1"."dbo"."CobroVEstCobro" "CobroVEstCobro" RIGHT OUTER JOIN "VFIN1"."dbo"."CobroV" "CobroV" ON "CobroVEstCobro"."IdFinca" = "CobroV"."IdFinca" AND "CobroVEstCobro"."IdDepto" = "CobroV"."IdDepto" AND "CobroVEstCobro"."IdInquilino" = "CobroV"."IdInquilino" AND "CobroVEstCobro"."NumRecibo" = "CobroV"."NumRecibo" AND "CobroVEstCobro"."FechaExped" = "CobroV"."FechaExped" } WHERE "CobroV"."FechaExped" > {d '2022-11-01' } GROUP BY "CobroV"."Id_Cobro"

When I try executing the query I get the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column ‘VFIN1.dbo.CobroV.NumRecibo’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This other query on the other side works fine:

SELECT "CobroV"."Id_Cobro", SUM( "CobroVEstCobro"."Importe" ) "Pagat" FROM { oj "VFIN1"."dbo"."CobroVEstCobro" "CobroVEstCobro" RIGHT OUTER JOIN "VFIN1"."dbo"."CobroV" "CobroV" ON "CobroVEstCobro"."IdFinca" = "CobroV"."IdFinca" AND "CobroVEstCobro"."IdDepto" = "CobroV"."IdDepto" AND "CobroVEstCobro"."IdInquilino" = "CobroV"."IdInquilino" AND "CobroVEstCobro"."NumRecibo" = "CobroV"."NumRecibo" AND "CobroVEstCobro"."FechaExped" = "CobroV"."FechaExped" } WHERE "CobroV"."FechaExped" > {d '2022-11-01' } GROUP BY "CobroV"."Id_Cobro"

The only aparent diference between the two is that the first one selects the field “CobroV”.“NumRecibo” while the second one does not.

Any Idea on how I can make this query work?

Villeroy · February 17, 2023, 9:44pm

Add “CobroV”.“NumRecibo” to the GROUP BY clause or aggregate “CobroV”.“NumRecibo”.

SELECT A,B,C, func(X), func(Y), func(Z)
FROM somewhere
GROUP BY A,B,C

selects the calculated values for X,Y and Z for each exitising combination of A,B,C. The count of rows returned by the query depends on the unique combinations of A,B,C.
If you add another column D to the selection without using an aggregating function and without adding it to the GROUP BY clause, the database does not know what to do with D.

osantos · February 18, 2023, 9:59am

I already tried doing that but it didn’t work… I tried it again and it now works. -the difference is that initially the fields A,B,C where calculated fields.

Is it not possible to group using calculated fields?

For example, one of my fields was the result of CONCAT(“inquilino”."nombre, ’ ', “inquilino”."apellido) as Inquilino, but when trying to group using the resulting field, it fails

Villeroy · February 18, 2023, 10:14am

SELECT X, SUM(B) 
FROM (
    SELECT A,B,C, func(X), func(Y), func(Z)
    FROM somewhere
    GROUP BY A,B,C
)
GROUP BY X

or if you saved the inner query as “Q1”:

SELECT X, SUM(B) 
FROM Q1
GROUP BY X