Count distinct records in Firebird 3.0

Question

I have data as follows:

  nota | produto | descricao | vendida | total | ...
     1         A         AAA       100    1000
     1         B         BBB        20     200
     1         A         AAA       200    2000
     1         C         CCC        50     500
     2         A         AAA       500    5000
     3         Z         ZZZ       100    1000
     4         X         XXX       100    1000
     4         B         BBB      1000   10000

I'm making a "produto" ranking with "vendida" (quantity) decreasing.

My select is as follows, and it works perfectly:

  with a as (
     select
        trim(ni.cd_produto) cd_produto,
        trim(p.ds_produto) ds_produto,
        sum(ni.qt_vendida) qt_vendida,
        sum(ni.vr_totalliquido) vr_totapagar
     from
        notaitem ni
     inner join nota n on n.cd_empresa = 1 and
                          n.cd_filial = 1 and
                          n.nr_nota = ni.nr_nota and
                          n.nr_desdobramento = ni.nr_desdobramento
     inner join produto p on p.cd_produto = ni.cd_produto
     where
        n.fg_situacao = 'N' and
        n.fg_situacaonfe = '1' and
        n.cd_tipomovto = 1 and
        n.dt_emissao between :pidt_inicio and :pidt_final
     group by
        ni.cd_produto,
        p.ds_produto
     order by
        qt_vendida desc
     )
  select
     dense_rank() over(order by qt_vendida desc) nr_rank,
     cd_produto,
     ds_produto,
     qt_vendida,
     vr_totapagar,
     cast(round(100 * qt_vendida / sum(qt_vendida) over(), 4) as numeric(7, 4)) pc_participacaoqtd,
     cast(round(100 * vr_totapagar / sum(vr_totapagar) over(), 4) as numeric(7, 4)) pc_participacaovr
  from
     a
  order by
     nr_rank,
     ds_produto

Using CTE to be able to use dense_rank, I can perfectly filter the items with the largest quantities sold in a given period.

The output of the initial example would be:

  nr_rank | cd_produto | ds_produto | qt_vendida | vr_totapagar | ...
        1            B          BBB         1020          10200
        2            A          AAA          800           8000
        3            X          XXX          100           1000
        4            Y          YYY          100           1000
        5            C          CCC           50            500

There is a new need to show how many times the "produto" appears in different "nota", and it may appear more than once in the same "nota", but if this happens it should be considered just one appearance.

In the case of "produto" A, it appears twice in "nota" 1 and once in "nota" 2, so it would be present in two different "nota", having a appearance of 2.

The new output would have the following format:

  nr_rank | cd_produto | ds_produto | qt_vendida | vr_totapagar | appearance | ...
        1            B          BBB         1020          10200            2
        2            A          AAA          800           8000            2
        3            X          XXX          100           1000            1
        4            Y          YYY          100           1000            1
        5            C          CCC           50            500            1

I tried using count(1), but as expected it would count all the rows, so the appearance, for example, of "produto" A would be 3 and not 2. I also tried count(distint) but all the "produto" had an appearance of 1. As follow:

  with a as (
     select
        trim(ni.cd_produto) cd_produto,
        trim(p.ds_produto) ds_produto,
        sum(ni.qt_vendida) qt_vendida,
        sum(ni.vr_totalliquido) vr_totapagar,
        count(1) appearance
     from
        notaitem ni
     inner join nota n on n.cd_empresa = 1 and
                          n.cd_filial = 1 and
                          n.nr_nota = ni.nr_nota and
                          n.nr_desdobramento = ni.nr_desdobramento
     inner join produto p on p.cd_produto = ni.cd_produto
     where
        n.fg_situacao = 'N' and
        n.fg_situacaonfe = '1' and
        n.cd_tipomovto = 1 and
        n.dt_emissao between :pidt_inicio and :pidt_final
     group by
        ni.cd_produto,
        p.ds_produto
     order by
        qt_vendida desc
     )
  select
     dense_rank() over(order by qt_vendida desc) nr_rank,
     cd_produto,
     ds_produto,
     qt_vendida,
     vr_totapagar,
     appearance,
     cast(round(100 * qt_vendida / sum(qt_vendida) over(), 4) as numeric(7, 4)) pc_participacaoqtd,
     cast(round(100 * vr_totapagar / sum(vr_totapagar) over(), 4) as numeric(7, 4)) pc_participacaovr
  from
     a
  order by
     nr_rank,
     ds_produto

Answer 1

I believe you are looking for is COUNT(DISTINCT <expression>). For this specific query you need COUNT(DISTINCT ni.nr_nota).