Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql count return Zero if no record found

Tags:

mysql

zero

I have a two tables.

cities - id_city, city_name
properties - id_property, id_city, property_name

I want to display cities.city_name and next to it [properties.count(id_city)]

How do I make a query that still returns zero if no records are found instead of NULL, so that I get results like this:

London [123]
New York [0]
Berlin [11]

where "New York" is [0], not NULL and not 1?

like image 700
David King Avatar asked Oct 06 '09 23:10

David King


People also ask

How do you show zero as count if there is no record in database?

you can use ISNULL or COALESCE:both are same with a small difference. ISNULL(param1,param2): can contains only 2 parameter, and there are no condition of having it's value.

HOW DO I GET SUM function in MySQL to return 0 if no values are found?

To return Sum as '0' if no values are found, use IFNULL or COALESCE commands. The following is the syntax for IFNULL. SELECT IFNULL(SUM(NULL), 0) AS aliasName; Let us now implement the above syntax in the following query.

How do you show months if it has no record and force it to zero if NULL on MySQL?

SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at); You can append that into your query like: SELECT IFNULL(SUM(total),0) as total_orders, mnt from (SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at)) mn LEFT JOIN orders o ON mn.

Does count Return 0 SQL?

The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.


2 Answers

I think the following will do it for you, though I haven't tested it. The trick is to get the property counts in one table, and then to left join that table to the cities table, converting NULLs to 0s using the IFNULL function.

SELECT city_name, IFNULL(property_count, 0)
FROM cities
LEFT JOIN
   (SELECT id_city, count(*) as property_count
    FROM properties
    GROUP BY id_city) city_properties
   USING (id_city);
like image 93
Shawn Avatar answered Oct 11 '22 19:10

Shawn


Use an outer join:

select cities.city_name, count(properties.id_city)
  from cities left join properties on cities.id_city = properties.id_city
  group by 1
like image 25
ChssPly76 Avatar answered Oct 11 '22 18:10

ChssPly76