CODEWITHSUNDEEP
sqlfirebird
diego pereira
diego pereira

Reputation: 21

Firebird group by , count and extract giving having Invalid expression

Could someone help with my sql? I want to count how many registrys with same name I have in the table HISTORICO_ACESSO.

My sql for now is:

select
    (EXTRACT(DAY FROM ha.entrada)||'.'||EXTRACT(month FROM ha.entrada)||'.'||extract(YEAR FROM ha.entrada)) as DATA,
    coalesce(hae.os_exame, '-') as EXAME,
    coalesce((select nome from usuario u where ha.login = u.usuario), ha.login) as USUARIO,
    coalesce(hae.pagina, 'Visualização Listagem') as TIPO,
    COUNT(*) as QTDE

from
    historico_acesso ha
    left join historico_acesso_exame hae
    on ha.codigo = hae.cod_historico

group by
    DATA, EXAME,  USUARIO, TIPO

And I having error:

Dynamic SQL Error. SQL error code = -104. Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

My DDL is:

CREATE TABLE HISTORICO_ACESSO (
    CODIGO        INTEGER NOT NULL,
    COD_REGISTRO  VARCHAR(200),
    USER_AGENT    VARCHAR(500),
    LOGIN         VARCHAR(100),
    NIVEL         VARCHAR(10),
    ENTRADA       TIMESTAMP NOT NULL,
    SAIDA         TIMESTAMP,
    IDIOMA        VARCHAR(10),
    INFO_EXTRAS   BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

ALTER TABLE HISTORICO_ACESSO ADD PRIMARY KEY (CODIGO);

Upvotes: 0

Views: 864

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109001

For this query you either need to use a derived table to create those aliases before grouping on them, or you need to group by column index, so either use:

select DATA, EXAME, USUARIO, TIPO, COUNT(*) as QTDE
from (
    select
        (EXTRACT(DAY FROM ha.entrada)||'.'||EXTRACT(month FROM ha.entrada)||'.'||extract(YEAR FROM ha.entrada)) as DATA,
        coalesce(hae.os_exame, '-') as EXAME,
        coalesce(u.nome, ha.login) as USUARIO,
        coalesce(hae.pagina, 'Visualização Listagem') as TIPO
    from historico_acesso ha
    left join historico_acesso_exame hae
        on ha.codigo = hae.cod_historico
    left join usuario u
        on ha.login = u.usario
) a
group by DATA, EXAME, USUARIO, TIPO

Note that I have also changed the sub-select on usuario to a left join instead (not really necessary, just my preference). Use of derived tables requires Firebird 2.0 or higher.

Or, replace your group by column name with a group by column index:

group by 1, 2, 3, 4

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I don't think Firebird supports aliases in the group by. Try this:

select (EXTRACT(DAY FROM ha.entrada)||'.'||EXTRACT(month FROM ha.entrada)||'.'||extract(YEAR FROM ha.entrada)) as DATA,
       coalesce(hae.os_exame, '-') as EXAME,
       coalesce((select nome from usuario u where ha.login = u.usuario), ha.login) as USUARIO,
       coalesce(hae.pagina, 'Visualização Listagem') as TIPO,
       COUNT(*) as QTDE
from historico_acesso ha left join
     historico_acesso_exame hae
     on ha.codigo = hae.cod_historico
group by (EXTRACT(DAY FROM ha.entrada)||'.'||EXTRACT(month FROM ha.entrada)||'.'||extract(YEAR FROM ha.entrada)),
         coalesce(hae.os_exame, '-'),
         coalesce((select nome from usuario u where ha.login = u.usuario), ha.login),
         coalesce(hae.pagina, 'Visualização Listagem');

Upvotes: 0

Related Questions