COUNT() query does not work

Question

The purpose of the query is to list, within a user-defined period, all states and their total number of independent quotes if zero. Customers must be active (status 30) and have city id number mentioned.

The table vangebot has the quotations, with id of the customer’s address and date of issue:

angebotid | eingabeDatum | adressebestellerid - nº do endereço
201970110 | 01/06/2019   | 26087
201970111 | 25/05/2018   | 15305
201970112 | 23/06/2016   | 18447

The table gpartner has information about the customer:

partnerid | partnername | status
24270     | Oliveira    | 30
24669     | Medsíntese  | 30
24665     | Assessoria  | 30
21122     | X           | 30

The table gadresse has the address linked to the client in the table gpartner, with the city number:

adresseid | partnerid | adresseName  | postfachplz - nº da cidade
26087     | 24270     | R. Alviverde | 193
15305     | 24669     | R. 358       | 2043
18447     | 24665     | Av. Leopoldo | 1890
14151     | 21122     | R. Barroca   | 30

The table its_microRegion has the city and the micro-region linked according to the address mentioned in the table gadresse:

id   | city           | uf - nº da uf
193  | Porto Alegre   | 23
2043 | Belo Horizonte | 17
1890 | Rio de Janeiro | 19
30   | Alecrim        | 12

The table its_Region has the name of the state, linked to the table its_microRegion:

id | uf | regionName
23 | RS | Rio Grande do Sul
17 | MG | Minas Gerais
19 | RJ | Rio de Janeiro
12 | GO | Goiás

I have drawn up the following consultation:

select its_Region.uf, its_Region.regionName, count(vangebot.angebotid) as qtdCotacoes from vangebot
left join gpartner on vangebot.kundeid = gpartner.partnerid
left join gadresse on gadresse.adresseid = vangebot.adressebestellerid
left join its_microRegion on gadresse.postfachplz = its_microRegion.id
inner join its_Region on its_microRegion.uf = its_Region.id
where vangebot.eingabeDatum between '2016-01-01' and '2019-06-02'
and gpartner.status = 30 and gadresse.postfachPlz <> ''
group by its_Region.uf order by qtdCotacoes desc;

Upshot:

uf | regionName        | qtdCotacoes
RS | Rio Grande do Sul | 1
MG | Minas Gerais      | 1
RJ | Rio de Janeiro    | 1

What should I show:

uf | regionName        | qtdCotacoes
RS | Rio Grande do Sul | 1
MG | Minas Gerais      | 1
RJ | Rio de Janeiro    | 1
GO | Goiás             | 0

However, the result presented only mentions the states that have linked quotations and the others do not. What should I do?

DB Fiddle code:

Consultation COUNT

Answer 1

Try to select with join only between tables its_region and its_microRegion to get all the states. The other tables with left join:

SELECT ITR.uf, ITR.regionName, COUNT(VAN.kundeid) AS qtdCotacoes
from its_Region ITR
inner join its_microRegion IMR
on IMR.uf = ITR.id
LEFT join gadresse GA on GA.postfachplz = IMR.id
LEFT join gpartner GP on GP.partnerid = GA.partnerid
LEFT JOIN vangebot VAN ON GP.partnerid = VAN.kundeid 
where (van.angebotid IS NULL OR van.eingabeDatum BETWEEN '2016-01-01' and '2019-06-02') AND 
gp.partnerid IS NULL OR gp.status = 30 AND 
ga.postfachPlz <> ''
 group by ITR.uf, ITR.regionName, VAN.kundeid
 order by qtdCotacoes desc;

OR with a subquery:

SELECT ITR.uf, ITR.regionName, 
(SELECT COUNT(VAN.kundeid) 
 FROM vangebot AS VAN 
 INNER JOIN gpartner AS GP on VAN.kundeid = GP.partnerid
INNER JOIN gadresse AS GA ON GP.partnerid = GA.partnerid 
WHERE VAN.eingabedatum between '2016-01-01' and '2019-06-02' AND
    GP.status = 30 and GA.postfachPlz <> '' AND GA.postfachplz = IMR.id
) AS qtdCotacoes
FROM its_Region AS ITR
INNER JOIN its_microRegion AS IMR
ON IMR.uf = ITR.id
ORDER BY qtdCotacoes DESC;

Answer 2

To get the desired result you must use in the clause FROM the table containing the main data, which in this case are the States. So change the order of your JOINs to get the rest of the data from it. It is important to use the LEFT to remove the requirement. No WHERE check the fields only if the id of the table in question is NULL, ensuring that even the State that has no related data is shown:

SELECT its_Region.uf,
       its_Region.regionName,
       COUNT(vangebot.angebotid) AS qtdCotacoes
  FROM its_Region
  LEFT JOIN its_microRegion ON its_microRegion.uf = its_Region.id
  LEFT JOIN gadresse ON gadresse.postfachplz = its_microRegion.id
  LEFT JOIN vangebot ON gadresse.adresseid = vangebot.adressebestellerid
  LEFT JOIN gpartner ON vangebot.kundeid = gpartner.partnerid
 WHERE (vangebot.angebotid IS NULL OR vangebot.eingabeDatum BETWEEN '2016-01-01' and '2019-06-02')
   AND (gpartner.partnerid IS NULL OR gpartner.status = 30)
   AND COALESCE(gadresse.postfachPlz, '') <> ''
 GROUP BY its_Region.uf
 ORDER BY qtdCotacoes DESC

Which will result in:

| uf  | regionName        | qtdCotacoes |
| --- | ----------------- | ----------- |
| RJ  | Rio de Janeiro    | 1           |
| RS  | Rio Grande do Sul | 1           |
| MG  | Minas Gerais      | 1           |
| GO  | Goiás             | 0           |

---

See working on DB Fiddle.