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.
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;
join requires that there be at least one match.