Why "Not contained in either an aggregate function or the Group By clause"?

17 views
Skip to first unread message

Luigi Siciliano

unread,
Jan 5, 2021, 11:01:22 AM1/5/21
to firebird...@googlegroups.com
Hello,

  this query works:

SELECT
  a.ID,
  a.DESCRIZIONE,
  SUM(DC.QUANTITA) AS QUANTITA,
  SUM(DC.IMPORTO) * 1.000 AS IMPORTO
FROM
  DOC_TESTA DT
  JOIN DOC_CORPO DC ON DT.ID = DC.DOC_TESTA_ID
  JOIN CLIENTI C ON DT.CLIENTE_ID = C.ID
  JOIN ARTICOLI A ON A.ID = DC.ARTICOLO_ID
WHERE
  C.DENOMINAZIONE CONTAINING 'Tecnocolor'
  AND ((DT.DOCUMENTO_ID = 'FEI') OR (DT.DOCUMENTO_ID = 'FED') OR
(DT.DOCUMENTO_ID = 'NCE'))
  AND (EXTRACT (YEAR FROM DT.DATA) = 2020)
  AND A.ID STARTING WITH 'KNF'
GROUP BY
  a.ID,
  a.DESCRIZIONE

but I want that the SUM(DC.QUANTITA) and SUM(DC.IMPORTO) are subtract if
DT.DOCUMENTO_ID = 'NCE' and I modified the query in that:

SELECT
  a.ID,
  a.DESCRIZIONE,
  IIF(DT.DOCUMENTO_ID = 'NCE', SUM(DC.QUANTITA) * -1.0000,
SUM(DC.QUANTITA)) AS QUANTITA,
  IIF(DT.DOCUMENTO_ID = 'NCE', SUM(DC.IMPORTO) * -1.000,
SUM(DC.IMPORTO) * 1.000) AS IMPORTO
FROM
  DOC_TESTA DT
  JOIN DOC_CORPO DC ON DT.ID = DC.DOC_TESTA_ID
  JOIN CLIENTI C ON DT.CLIENTE_ID = C.ID
  JOIN ARTICOLI A ON A.ID = DC.ARTICOLO_ID
WHERE
  C.DENOMINAZIONE CONTAINING 'Tecnocolor'
  AND ((DT.DOCUMENTO_ID = 'FEI') OR (DT.DOCUMENTO_ID = 'FED') OR
(DT.DOCUMENTO_ID = 'NCE'))
  AND (EXTRACT (YEAR FROM DT.DATA) = 2020)
  AND A.ID STARTING WITH 'KNF'
GROUP BY
  a.ID,
  a.DESCRIZIONE

But in this case I obtain an exception: "Not contained in either an
aggregate function or the Group By clause"!

There are somebody to help me to understand why and how I must modify
the query to obtain right result?

Thanks.

--
Luigi Siciliano
--------------------------

AVVISO DI RISERVATEZZA

Le informazioni contenute nella presente e negli allegati, sono segrete e riservate al destinatario indicato. A chi legge il presente avviso - se non è l'effettivo destinatario, o un dipendente o una persona responsabile della consegna della comunicazione - si notifica che è proibito qualsiasi uso, copia, distribuzione o divulgazione di quanto in essa contenuto ai sensi dell'art. 616 C.P. e di quanto stabilisce il D. Lgs 196/03 sulla tutela dei dati personali.

Se questa comunicazione vi è pervenuta per errore, vi preghiamo di informarci al numero di telefono: +39 0934 465217.

Il contenuto della presente e di quanto allegato, non va letto, ma rispeditoci per posta o eliminato immediatamente.

Omacht András

unread,
Jan 5, 2021, 11:12:33 AM1/5/21
to firebird...@googlegroups.com
Hello,

try this:

SUM(DC.QUANTITA * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
AS QUANTITA,
SUM(DC. IMPORTO * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
AS IMPORTO,


CÉGÜNK A LIBRA CSOPORT TAGJA
OMACHT ANDRÁS
fejlesztési igazgató

LIBRA Szoftver Zrt.
1113 Budapest, Karolina út 65.
+36 (1) 255-3939
in...@libra.hu | www.libra.hu
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/7d0503df-4934-8f50-9c10-cd655bdcf90f%40tiscalinet.it.



__________ Information from ESET Mail Security, version of virus signature database 22595 (20210105) __________

The message was checked by ESET Mail Security.
http://www.eset.com


Luigi Siciliano

unread,
Jan 5, 2021, 11:23:58 AM1/5/21
to firebird...@googlegroups.com
Hello,

Il 05/01/2021 17:08, Omacht András ha scritto:
> SUM(DC.QUANTITA * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
> AS QUANTITA,
> SUM(DC. IMPORTO * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
> AS IMPORTO,

I had tried before posting in the list: Is the same exception :(

Thanks

--
Luigi Siciliano
--------------------------

Mark Rotteveel

unread,
Jan 5, 2021, 11:32:23 AM1/5/21
to firebird...@googlegroups.com
On 05-01-2021 17:23, Luigi Siciliano wrote:
> Hello,
>
> Il 05/01/2021 17:08, Omacht András ha scritto:
>> SUM(DC.QUANTITA * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
>>   AS QUANTITA,
>> SUM(DC. IMPORTO * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
>>   AS IMPORTO,
>
> I had tried before posting in the list: Is the same exception :(

That could potentially be a bug in Firebird, consider reporting it in
the tracker (assuming it's reproducible in 3.0.7).

However, the error in your original query is definitely intended,
because in the context of IIF(DT.DOCUMENTO_ID = 'NCE', SUM(DC.QUANTITA)
* -1.0000, SUM(DC.QUANTITA)) AS QUANTITA, there is no DT.DOCUMENTO_ID,
because it is not in the GROUP BY.

The workaround for both is to use a nested table expression:

SELECT
x.ID, x.DESCRIZIONE,
SUM(x.QUANTITA) AS QUANTITA,
SUM(x.IMPORTO) AS IMPORTO
FROM (
SELECT a.ID, a.DESCRIZIONE,
IIF(DT.DOCUMENTO_ID = 'NCE', DC.QUANTITA * -1.0000, DC.QUANTITA *
1.000) AS QUANTITA,
IIF(DT.DOCUMENTO_ID = 'NCE', DC.IMPORTO * -1.000, DC.IMPORTO * 1.000)
AS IMPORTO
FROM DOC_TESTA DT
JOIN DOC_CORPO DC ON DT.ID = DC.DOC_TESTA_ID
JOIN CLIENTI C ON DT.CLIENTE_ID = C.ID
JOIN ARTICOLI A ON A.ID = DC.ARTICOLO_ID
WHERE C.DENOMINAZIONE CONTAINING 'Tecnocolor'
AND ((DT.DOCUMENTO_ID = 'FEI') OR (DT.DOCUMENTO_ID = 'FED') OR
(DT.DOCUMENTO_ID = 'NCE'))
AND (EXTRACT (YEAR FROM DT.DATA) = 2020)
AND A.ID STARTING WITH 'KNF'
) x
GROUP BY x.ID, x.DESCRIZIONE

Mark
--
Mark Rotteveel

Luigi Siciliano

unread,
Jan 5, 2021, 12:17:43 PM1/5/21
to firebird...@googlegroups.com
Hello,

Il 05/01/2021 17:32, Mark Rotteveel ha scritto:
>
> That could potentially be a bug in Firebird, consider reporting it in
> the tracker (assuming it's reproducible in 3.0.7).

I use version 2.5.9. I report the potentially bug in what section:
Firebird Core?


> However, the error in your original query is definitely intended,
> because in the context of IIF(DT.DOCUMENTO_ID = 'NCE',
> SUM(DC.QUANTITA) * -1.0000, SUM(DC.QUANTITA)) AS QUANTITA, there is no
> DT.DOCUMENTO_ID, because it is not in the GROUP BY.

Ok, I understand. But then why do you think that is a potentially bug?


> The workaround for both is to use a nested table expression:
>
Works :)

Dimitry Sibiryakov

unread,
Jan 5, 2021, 12:21:49 PM1/5/21
to firebird...@googlegroups.com
05.01.2021 18:17, Luigi Siciliano wrote:
> I use version 2.5.9. I report the potentially bug in what section: Firebird Core?

Before that you must show you exact query, not just words "I tried it".

--
WBR, SD.

Mark Rotteveel

unread,
Jan 5, 2021, 12:46:11 PM1/5/21
to firebird...@googlegroups.com
On 05-01-2021 18:17, Luigi Siciliano wrote:
> Il 05/01/2021 17:32, Mark Rotteveel ha scritto:
>>
>> That could potentially be a bug in Firebird, consider reporting it in
>> the tracker (assuming it's reproducible in 3.0.7).
>
> I use version 2.5.9. I report the potentially bug in what section:
> Firebird Core?

I couldn't reproduce the problem you say you had when trying the
alternative suggested by Omacht András in 2.5.9 or 3.0.7, see below, so
reporting a bug is probably not useful, unless you show exactly what
alternative you tried and what error it produced.

>> However, the error in your original query is definitely intended,
>> because in the context of IIF(DT.DOCUMENTO_ID = 'NCE',
>> SUM(DC.QUANTITA) * -1.0000, SUM(DC.QUANTITA)) AS QUANTITA, there is no
>> DT.DOCUMENTO_ID, because it is not in the GROUP BY.
>
> Ok, I understand. But then why do you think that is a potentially bug?

The solution suggested by Omacht András should work, but you said it
didn't work. So if that didn't work, that could potentially be a bug.

However the the error when using IIF(DT.DOCUMENTO_ID = 'NCE',
SUM(DC.QUANTITA) * -1.0000, SUM(DC.QUANTITA)) is expected and intended,
because with the specified GROUP BY, DT.DOCUMENTO_ID is not available to
your query at this point (as it is used **outside** the SUM, not
**inside** the SUM).

However, I have just tested the equivalent of SUM(IIF(DT.DOCUMENTO_ID =
'NCE', DC.QUANTITA * -1.000, DC.QUANTITA * 1.000)) suggested by Omacht
András, and this works fine in both 2.5.9 and 3.0.7, so whatever you
tried that didn't work, it was not that.

So, you probably haven't found a bug in Firebird, but a bug in your code.

Mark
--
Mark Rotteveel

Svein Erling Tysvær

unread,
Jan 5, 2021, 12:50:27 PM1/5/21
to firebird...@googlegroups.com
Does

SELECT
   a.ID,
   a.DESCRIZIONE,
   SUM( IIF( DT.DOCUMENTO_ID = 'NCE', DC.QUANTITA * -1.0000, DC.QUANTITA ) ) AS QUANTITA,
   SUM( IIF( DT.DOCUMENTO_ID = 'NCE', DC.IMPORTO  * -1.000,  DC.IMPORTO * 1.000 ) AS IMPORTO
...
work?

If this table contains or will contain lots of data for many years, I would also use

AND DT.DATA >= '2020-01-01'
AND DT.DATA <  '2021-01-01'

since this can use an index, whereas EXTRACT(YEAR FROM DT.DATA) = 2020 cannot do so.

HTH,
Set

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

Luigi Siciliano

unread,
Jan 6, 2021, 11:15:45 AM1/6/21
to firebird...@googlegroups.com
Hello,

Il 05/01/2021 18:46, Mark Rotteveel ha scritto:
> The solution suggested by Omacht András should work, but you said it
> didn't work. So if that didn't work, that could potentially be a bug.

> However, I have just tested the equivalent of SUM(IIF(DT.DOCUMENTO_ID
> = 'NCE', DC.QUANTITA * -1.000, DC.QUANTITA * 1.000)) suggested by
> Omacht András, and this works fine in both 2.5.9 and 3.0.7, so
> whatever you tried that didn't work, it was not that.
>
> So, you probably haven't found a bug in Firebird, but a bug in your code.

You are right, sorry

I revised the solution suggested by Omacht András that seemed that
identical to the one I had tried before posting but due a mistake with
the parentheses not worked. Below the wrong code that seemed identical:

SUM(DC.QUANTITA) * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000) AS
QUANTITA,
SUM(DC. IMPORTO) * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000) AS IMPORTO

Luigi Siciliano

unread,
Jan 6, 2021, 11:15:54 AM1/6/21
to firebird...@googlegroups.com
Hello,

Il 05/01/2021 17:23, Luigi Siciliano ha scritto:
> Hello,
>
> Il 05/01/2021 17:08, Omacht András ha scritto:
>> SUM(DC.QUANTITA * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
>>   AS QUANTITA,
>> SUM(DC. IMPORTO * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000))
>>   AS IMPORTO,
>
> I had tried before posting in the list: Is the same exception :(
>
Oops, sorry. :-[

I had tried a solution that seemed identical to the one you suggested
and instead was different in parentheses:

  SUM(DC.QUANTITA) * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000) AS
QUANTITA,
  SUM(DC. IMPORTO) * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000) AS
IMPORTO

Dimitry Sibiryakov

unread,
Jan 6, 2021, 11:18:35 AM1/6/21
to firebird...@googlegroups.com
06.01.2021 17:15, Luigi Siciliano wrote:
> I had tried a solution that seemed identical to the one you suggested and instead was
> different in parentheses:
>
>   SUM(DC.QUANTITA) * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000) AS QUANTITA,
>   SUM(DC. IMPORTO) * IIF(DT.DOCUMENTO_ID = 'NCE', -1.0000, 1.0000) AS IMPORTO

It does not work because DT.DOCUMENTO_ID is neither in GROUP BY list nor inside of an
aggregate function.
IIF() is not an aggregate function.

--
WBR, SD.

Luigi Siciliano

unread,
Jan 6, 2021, 11:30:11 AM1/6/21
to firebird...@googlegroups.com

Hello,

Yes, now I understand. I just wanted to tell Omacht András that his suggestion was right, it was I who was wrong to say that it didn't work because the code seemed identical to a test I had done and instead due to the brackets it was different :(

Thanks

-- 
Luigi Siciliano
--------------------------

AVVISO DI RISERVATEZZA

  Le informazioni contenute nella presente e negli allegati, sono segrete e riservate al destinatario indicato. A chi legge il presente avviso - se non è l'effettivo destinatario, o un dipendente o una persona responsabile della consegna della comunicazione - si notifica che è proibito qualsiasi uso, copia, distribuzione o divulgazione di quanto in essa contenuto ai sensi dell'art. 616 C.P. e di quanto stabilisce il D. Lgs 196/03 sulla tutela dei dati personali.

  Se questa comunicazione vi è pervenuta per errore, vi preghiamo di informarci al numero di telefono: +39 0934 465217.

  Il contenuto della presente e di quanto allegato, non va letto, ma rispeditoci per posta o eliminato immediatamente.

P-Soft - Fabio Codebue

unread,
Jan 6, 2021, 11:31:39 AM1/6/21
to firebird...@googlegroups.com
Ottimo Luigi sono contento che la community abbia trovato la soluzione



Fabio Codebue

Firebird Foundation Commitee Memeber


P-SOFT di Codebue Fabio

Via Nuova n. 9 - 24060 Tavernola B.sca (BG)

P.I. 03624950162

C.F. CDBFBA72A11C618T

Mobile: +39.348.3515786

Fax: +39.030.5100306

Web: http://www.p-soft.biz

 

pec: amminis...@pec.p-soft.biz
cod.intermediario SDI: KRRH6B9
 


------ Messaggio originale ------
Da: "Luigi Siciliano" <luig...@tiscalinet.it>
Inviato: 06/01/2021 17:30:10
Oggetto: Re: [firebird-support] Why "Not contained in either an aggregate function or the Group By clause"?
--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages