Check values with 0 included in Count() even if they do not have values

Asked

Viewed 498 times

4

I need to consult the number of banknotes issued with template 55 to create a report. The problem is that I’m needing to put zeroed values in the quantity column even though it has no value at all.

Ex:

empresa   filial   data   quantidade
  5         1     01/2012     30
  5         1     02/2012     42
  5         1     03/2012     30
  5         1     04/2012     52
  5         2     01/2012     33
  5         2     02/2012     42

However, I’ve consulted all year, specifically I’d like it to stay that way:

empresa   filial   data   quantidade
  5         1     01/2012     30
  5         1     02/2012     42
  5         1     03/2012     30
  5         1     04/2012     52
  5         1     05/2012     0
  5         1     06/2012     0
  5         1     07/2012     0
  5         1     08/2012     0
  5         1     09/2012     0
  5         1     10/2012     0
  5         1     11/2012     0
  5         1     12/2012     0
  5         2     01/2012     33
  5         2     02/2012     42
  5         2     03/2012     0
  5         2     04/2012     0
  5         2     05/2012     0
  5         2     06/2012     0
  5         2     07/2012     0
  5         2     08/2012     0
  5         2     09/2012     0
  5         2     10/2012     0
  5         2     11/2012     0
  5         2     12/2012     0

I’ve tried with coalesce(quantidade, 0), case when(quantidade is null) then quantidade = 0 and even some procedures, but I failed miserably.

In summary: It is possible to "force" the count() also inform the null values as 0 even without information?

Edit:

Consultations being held:
As requested I will put as I am doing, would put a fictitious code, but I will inform a real, I believe it is clearer. SQL query:

SELECT E.EMPRESA,F.NOMEFANTASIA,F.FILIAL,
EXTRACT(MONTH FROM S.DATASAIDA)||'/'||EXTRACT(YEAR FROM S.DATASAIDA) AS DATASAIDA,
S.MODELO, COUNT(*) AS QUANTIDADE
FROM ES02_SAIDA S
INNER JOIN SC00_FILIAL F ON (F.IDFILIAL = S.IDFILIAL)
INNER JOIN SC00_EMPRESA E ON (E.IDEMPRESA = F.IDEMPRESA)
WHERE E.EMPRESA IN (:IDEMPRESA)
AND S.DATASAIDA BETWEEN ''||:DATAINI||'' AND ''||:DATAFIM||''
AND (S.MODELO = '55' OR  S.MODELO = '57')
GROUP BY E.EMPRESA,F.NOMEFANTASIA,F.FILIAL,
EXTRACT(MONTH FROM S.DATASAIDA)||'/'||EXTRACT(YEAR FROM S.DATASAIDA),
S.MODELO
ORDER BY 5,1,2

Upshot:

EMPRESA  NOMEFANTASIA   FILIAL  DATASAIDA  MODELO  QUANTIDADE
  5  EMPRESA             1  1/2012     55              44  
  5  EMPRESA             1  2/2012     55              34  
  5  EMPRESA             1  3/2012     55              53  
  5  EMPRESA             1  4/2012     55              48  
  5  EMPRESA             1  5/2012     55              76  
  5  EMPRESA             1  6/2012     55              39  
  5  EMPRESA             1  7/2012     55              22  
  5  EMPRESA             1  8/2012     55              34  
  5  EMPRESA             1  9/2012     55              32  
  5  EMPRESA             1  10/2012    55              39  
  5  EMPRESA             1  11/2012    55              38  
  5  EMPRESA             1  12/2012    55              77  
  5  EMPRESA2            2  1/2012     55              38  
  5  EMPRESA2            2  2/2012     55              59  
  5  EMPRESA2            2  3/2012     55              53  
  5  EMPRESA2            2  4/2012     55               8

Edit 2:

Good afternoon, thank you very much for your help and attempt to resolve my issue. But I come back with another question to you that would be about using the UNION. I am using the following SQL: 
SELECT TABELA.EMPRESA,
       TABELA.NOMEFANTASIA,
       TABELA.FILIAL,
       TABELA.DATASAIDA,
       TABELA.MODELO,
       COUNT(TABELA.MODELO) AS QUANTIDADE
FROM(
       SELECT E.EMPRESA,F.NOMEFANTASIA,F.FILIAL,
       EXTRACT(MONTH FROM S.DATASAIDA)||'/'||EXTRACT(YEAR FROM S.DATASAIDA) AS DATASAIDA,
       S.MODELO, COUNT(*) AS QUANTIDADE
       FROM ES02_SAIDA S
       INNER JOIN SC00_FILIAL F ON (F.IDFILIAL = S.IDFILIAL)
       INNER JOIN SC00_EMPRESA E ON (E.IDEMPRESA = F.IDEMPRESA)
       WHERE E.EMPRESA IN (:IDEMPRESA)
       AND S.DATASAIDA BETWEEN ''||:DATAINI||'' AND ''||:DATAFIM||''
       AND (S.MODELO = '55' OR  S.MODELO = '57')
       group by 1,2,3,4,5
   UNION
       SELECT distinct NULL AS EMPRESA,'TOTAIS POR FILIAL' AS NOMEFANTASIA,F.filial AS FILIAL,
       null AS DATASAIDA,null as MODELO,
       --'QTD='||(SUM(1))||' MÉDIA= '||(SUM(1)/COUNT(S.modelo)) AS QUANTIDADE
       (SUM(1)/COUNT(S.modelo)) AS QUANTIDADE
       FROM ES02_SAIDA S
       INNER JOIN SC00_FILIAL F ON (F.IDFILIAL = S.IDFILIAL)
       INNER JOIN SC00_EMPRESA E ON (E.IDEMPRESA = F.IDEMPRESA)
       WHERE E.EMPRESA IN (:IDEMPRESA)
       AND S.DATASAIDA not BETWEEN ''||:DATAINI||'' AND ''||:DATAFIM||''
       AND (S.MODELO = '55' OR  S.MODELO = '57')
       group by 1,2,3,4,5
)TABELA
    GROUP BY
    TABELA.EMPRESA,
    TABELA.NOMEFANTASIA,
    TABELA.FILIAL,
    TABELA.DATASAIDA,
    TABELA.MODELO
       ORDER BY
       TABELA.FILIAL asc nulls last,
       TABELA.DATASAIDA asc nulls last,
       TABELA.EMPRESA asc nulls last

The result is bringing the same results of the first Edit, but the amount is 1 in all and 0 in the totaling line.

EMPRESA  NOMEFANTASIA       FILIAL  DATASAIDA  MODELO  QUANTIDADE
  5  EMPRESA                 1       1/2012     55          1  
  5  EMPRESA                 1       10/2012    55          1  
  5  EMPRESA                 1       11/2012    55          1  
  5  EMPRESA                 1       12/2012    55          1  
  5  EMPRESA                 1       2/2012     55          1  
  5  EMPRESA                 1       3/2012     55          1  
  5  EMPRESA                 1       4/2012     55          1  
  5  EMPRESA                 1       5/2012     55          1  
  5  EMPRESA                 1       6/2012     55          1  
  5  EMPRESA                 1       7/2012     55          1  
  5  EMPRESA                 1       8/2012     55          1  
  5  EMPRESA                 1       9/2012     55          1  
     TOTAIS POR FILIAL       1                              0  
  5  EMPRESA                 2       1/2012     55          1  
  5  EMPRESA                 2       2/2012     55          1  
  5  EMPRESA                 2       3/2012     55          1  
  5  EMPRESA                 2       4/2012     55          1  
     TOTAIS POR FILIAL       2                              0

It was supposed to bring 44,34,53... ( Thank you all for your help

sql firebird

  • Hello Gaspar, could you post an example with some data and an example query (click the edit link on your question)? Apparently you have one group by empresa, filial, data right? Usually this involves a LEFT JOIN with the dates... Even if you have to generate this list of "months in each year" dynamically.

  • It seems unlikely to me that you will achieve this. If I understand correctly you want to select what you do not have. If you issue a specific command by selecting the date x then the return will be null and you could deal with the coalition, but if you ask to group, it will add up only those that exist, of course. You need another reset table and then you can do Join with it or a case to put intelligence in this select.

  • 1

    Hello, thanks for helping Anthony and Reginaldo. LEFT JOIN because it is not an existing column in the table. I tried to do with a LEFT JOIN ES02_SAIDA S2 ON (S2.IDSAIDA = S.IDSAIDA) but it didn’t work out either. group by is discarding the results you should report. UNION and it didn’t work either...

  • See if you can help http://forum.imasters.com.br/topic/531584-comort-results-zerates/

  • Really did not give, in this case the jamersondr in the forum imasters. The logic is for days, and does not confer date, only years. Even reporting this case in the 12 months would cause the group by disregard the zeroed values. Fought for information

  • I managed to partially arrange the consultation. Thanks for your help

Show 1 more comment

2 answers

2

For this kind of problem, I created a procedure that returns the days of a given period:

SET TERM ^ ;

create or alter procedure DIAS_PERIODO (
    INICIO date,
    FIM date)
returns (
    DIA date)
as
begin
  DIA = :INICIO;
  WHILE (DIA <= :FIM) DO
  BEGIN
    suspend;
    DIA = DIA + 1;
  END
end^

SET TERM ; ^

Then you can make an SQL in this style:

select   count(t.ID),
         d.DIA
from     (select dia from DIAS_PERIODO('01.01.2017', '31.01.2017')) d left JOIN
         tramitacao t on t.DATA = d.DIA
group by d.dia

(SQL different from yours because I tested here with a table of my system that has dates)

Upshot:

COUNT   DIA
0   01/01/2017
24  02/01/2017
23  03/01/2017
13  04/01/2017
22  05/01/2017
78  06/01/2017
0   07/01/2017
0   08/01/2017
55  09/01/2017
42  10/01/2017
27  11/01/2017
47  12/01/2017
94  13/01/2017
0   14/01/2017
0   15/01/2017
28  16/01/2017
101 17/01/2017
81  18/01/2017
49  19/01/2017
68  20/01/2017
0   21/01/2017
0   22/01/2017
90  23/01/2017
110 24/01/2017
89  25/01/2017
86  26/01/2017
126 27/01/2017
0   28/01/2017
0   29/01/2017
65  30/01/2017
154 31/01/2017

0

utilise HAVING before the order by add HAVING COUNT(*) = 0.

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.