How to get data based on row count in MySQL?

I am working on a project where I have to fetch data from database if the row count is greater than zero then show it otherwise don’t. But my query returning all rows.

This is My Query

SELECT
    d.id,
    d.district,
    (
    SELECT
        COUNT(a.district_id)
    FROM
        ambulance AS a
    WHERE
        a.district_id = d.id
) AS total
FROM
    district d
ORDER BY
    total
DESC

That is okay, but I added a WHERE clause with my query witch is,

WHERE total > 0

But I am having a sql error Unknown column ‘total’ in ‘where clause’

Now my question is, how can I achieve a result with WHERE total > 0, do I have to type something else in the place of total? What is the proper way to add this WHERE clause in my query.

Answer

MySQL extends the use of the HAVING clause, so it can be used in non-aggregation queries. This allows you to use an alias:

SELECT d.id, d.district,
      (SELECT COUNT(*)
       FROM ambulance a
       WHERE a.district_id = d.id
      ) AS total
FROM district d
HAVING total > 0
ORDER BY total DESC;

This logic would more colloquially be written using an inner join:

select d.id, d.district, count(*) as total
from district d join
     ambulance a
     on a.district_id = d.id
group by d.id, d.district 
order by total desc;

The join requires that there be at least one match.