Query values with 0 included in count () even if they do not have values

Question

Query values with 0 included in count () even if they do not have values

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

I need to check the number of notes issued with template 55 to create a report. The problem is that I'm having to put zero values in the quantity column even though they have 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 consulted the entire year, specifically I would like it to look like this:

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 have tried with coalesce(quantidade, 0) , case when(quantidade is null) then quantidade = 0 and even some procedures, but failed miserably.

In summary: Is it possible to "force" the count() to also inform the null values as 0 even having no information?

Edit:

Inquiries being carried out:
As requested I will put as I am doing, I would put a fictitious code, but I will inform a real one, I believe it becomes 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

Result:

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 the help and attempt to resolve my question. However, I'll come back with another question to you about using 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 as the first Edit, however the quantity is 1 in all and 0 in the totalizer 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 to bring 44,34,53 ... :( Thank you all for the help

sql firebird

asked by anonymous 13.12.2016 / 12:53

2 answers

Carousel with only CSS: problems with centering and setting size autocomplete="off" in input type "password" is required? Because?

score 1 · Answer 1

For this type 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 do a 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 differs from yours as I tested here with a table in my system that has dates)

Result:

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

score 0 · Answer 2

0

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

04.01.2017 / 20:50