I have the following query where it shows me the areas and, with a sub-query, counts the fixed assets of these:
SELECT
Nombre AS datos,
(
SELECT
COUNT(IdActivoFijo)
FROM
c_activofijotemporal AS ac
WHERE
IdArea = a.IdArea
) AS series
FROM
c_area AS a
WHERE
a.IdPeriodo = 40
ORDER BY
a.Nombre;
returns the following values:
Académicos 0
Administración 92
Amigos D. E 0
Amigos P 0
Amigos S. 0
Archivo 1
Arquitectura 0
Comunicación 0
Comunicación Social 0
Contabilidad 0
Coordinación Operativa 0
Custodios 139
My question is: How can I make it not show the records that are equal to 0?
Update: You must use the HAVING clause to be able to use the aggregation functions, and only return those whose SUM > 0. Let's group using name:
I tested with the following data:
And the results were:
One option would be to add a subquery in the from which returns data and series, so that the series field is conditioned > 0
example:
The simplest and most optimal solution I can think of is to make a
inner join
.It is not necessary to apply other conditions, because when an area does not have records in the table it
c_activofijotemporal
will automatically be excluded from the results, because it is ainner join
.Another option, if you can't modify the query that much, is to override the subquery
where
, but beware, the MySQL optimizer will probably run the subquery twice per record to do this, so performance can be poor if the volume of data is significant:If you see, in this second case, I have transferred the sub-query as it
where
is to filter those that have a value of 0.