Query COUNT (*) returning values without counting as zero

Question

Query COUNT (*) returning values without counting as zero

Navigation

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

0

I have a table where I have responsibility for incidents. I want to remove all those responsible with all the incidents of each one, but also those who do not have.
Table:

RESPONSABLES TIPO

Example with query results (I want responsible with request incidents):

Primer_responsable 2
Segundo_responsable 0
Tercer_responsable 10
Cuarto_responsable 5

I'll be clearer:

Each incident can be of four types: request, error, sub-task or incident.

And the people in charge are different people.

Attempt:

SELECT Responsable,COUNT(*)
FROM v_Reporte
WHERE 'Tipo de Incidencia' LIKE '%Peticion%'
GROUP BY Responsable;

But I do not get those that have no incidence of request with value 0.

I add an example, to make it even clearer: link

mysql

asked by Ignacio Martínez 16.04.2018 в 15:24

source

2 answers

0

I edit the answer with the correct sentence and with the corresponding SQLFiddle .

Query using LEFT OUTER JOIN :

SELECT DISTINCT tickets.Responsable, COUNT(responsables.responsable) 
Incidencias
FROM tickets LEFT OUTER JOIN
(    SELECT responsable
     FROM Tickets
     WHERE tipo_incidencia like 'Peticion%') responsables 
ON responsables.Responsable = tickets.Responsable
GROUP BY responsable

This way if the results are obtained.

I leave you also an image about the operation of the outer joins. this link where they explain their performance better than I would do ..

answered by 16.04.2018 в 16:05

Render two lists by commas in a bootstrap element by js Join duplicate rows in mysql

score 1 · Accepted Answer

A good option is to use a evaluation expression in the COUNT ():

COUNT (ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

SELECT Responsable,COUNT(case Tipo_Incidencia when 'Error' then 1 else NULL end)
FROM Tickets
GROUP BY Responsable;

Exit :

Responsible COUNT (case Type_Incidence when 'Error' then 1 else NULL   end)

Aurora 0

Maria 1

Mariano 1

Pedro 0

I leave here the SQLFiddle