Mysql Count() to Return 0 If No Records Found

MySql count() to return 0 if no records found

There is no record for the month of January that is why you are getting no result. One solution that works is by joining a subquery with contains list of months that you want to be shown on the list.

SELECT count(b.id) as totalRec
FROM   (
            SELECT 'January' mnth
            UNION ALL
            SELECT 'February' mnth
            UNION ALL
            SELECT 'March' mnth
        ) a
        LEFT JOIN post b
            ON a.mnth = DATE_FORMAT(b.date, '%M') AND
               year(b.date) =  '2013' AND 
               DATE_FORMAT(b.date, '%M') IN ('January', 'February', 'March') 
GROUP  BY year(b.date)-month(b.date) 
ORDER  BY b.date ASC

• SQLFiddle Demo

OUTPUT

╔══════════╗
║ TOTALREC ║
╠══════════╣
║        0 ║
║        7 ║
║        9 ║
╚══════════╝

Mysql count return Zero if no record found

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

MySql count () to return 0 if no records found

Move the criteria in the WHERE clause to the ON clause of the join:

SELECT c.cla_name, COUNT(p.cla_name)
FROM clase c
LEFT JOIN products p
    ON p.cla_name = c.cla_name AND
       p.product_type = 29
GROUP BY c.cla_name;

Because the WHERE filter is happening before the join, your current query removes the D name record from the clase table before the join happens.

Return 0 if no records found on COUNT(*) query In MYSQL

As long as there is a table Chats with field sender_id then the following will return a row even if the sender_Id 13 does not exist in the table.

set @sender = 13;
set @chats  = (select count(*) from Chat where sender_id = @sender);

select @sender AS sender_id, IFNULL(@chats,0) AS Count_chats
;

See this working at SQLFiddle

MySql count() how many of type per month and return 0 if no records found

Maybe you'll find ok a solution with a result like:
date_in total_items_claimed total_items_unclaimed

SELECT MONTH( date_in ) as date_in,
SUM(IF(claimed, 1, 0)) AS total_items_claimed,
SUM(IF(claimed, 0, 1)) AS total_items_unclaimed
FROM item
WHERE date_in >= NOW() - INTERVAL 1 YEAR
GROUP BY MONTH(date_in)

Hope you like it :)

Edited:

For retrieving monts with no records you'll have to cross the table with another created for that:

As seen at Create an inline SQL table on the fly (for an excluding left join) you could add a months_table:

SELECT 
  date_in, 
  total_items_claimed, 
  total_items_unclaimed, 
  COALESCE(total_items_claimed, 0) + COALESCE(total_items_unclaimed, 0) as total_items 
FROM (
  SELECT 
    MONTH( date_in ) as date_in,
    SUM(IF(claimed, 1, 0)) AS total_items_claimed,
    SUM(IF(claimed, 0, 1)) AS total_items_unclaimed
  FROM item
  WHERE date_in >= NOW() - INTERVAL 1 YEAR
  GROUP BY MONTH(date_in)
) as items_count LEFT JOIN (
  SELECT 1 AS MONTH UNION
  SELECT 2 AS MONTH UNION
  SELECT 3 AS MONTH UNION
  SELECT 4 AS MONTH UNION
  SELECT 5 AS MONTH UNION
  SELECT 6 AS MONTH UNION
  SELECT 7 AS MONTH UNION
  SELECT 8 AS MONTH UNION
  SELECT 9 AS MONTH UNION
  SELECT 10 AS MONTH UNION
  SELECT 11 AS MONTH UNION
  SELECT 12 AS MONTH 
) as months_table on items_count.date_in = months_table.month

I have not tested this query, good luck! :)

MySQL count does not return 0 if no record found

So Actually when you do that (which is what you do, reformulated for the JOIN):

SELECT post.post_id, COUNT(comment) 
FROM `comment`
INNER JOIN post ON `comment`.post_id = post.post_id 
GROUP BY post.post_id;

You gather only post rows having at least one reference in comment.

If you alter the JOIN type to a LEFT join, this way:

SELECT post.post_id, COUNT(comment) 
FROM `comment`
LEFT JOIN post ON `comment`.post_id = post.post_id 
GROUP BY post.post_id;

Then the rows from post are all there, and NULL values are inserted for columns of comments if no comments related to this row exists (that's a left join). So if comment is a column from table comment it will be there for each rows of post table, but with a NULL value, after the group by on the post_id column the subset of comments related to this post contains only 1 NULL value, the count should return 0.

select count(NULL);

returns 0.

Now you could use a subquery but that's a really bad idea, subqueries are usually done instead of LEFT JOINS, usually it's a mistake, sometimes it's not, but it's really often a mistake. When you do a left join indexes are used to compare the key values of the 2 tables (the ON clause) and build one final 'temporary' result of rows, mixing values from both tables (and then, or maybe in the same time, the filters from other parts of your queries are applied). When you use a subquery, for each row of the first table a new query is run to get results from the second table (not always, but it's another problem), the cost is reeeaaally bigger for the database engine.

count to return 0 if no records founds

You are filtering out rows that don't have any records for the given date. Just move the filtering into the LEFT JOIN part.

So instead of

LEFT JOIN elecciones_contactos as ec
 on ec.ID_Equipo_Detalle = eq.ID_Equipos_Detalle
WHERE ec.FechaAlta BETWEEN CURRENT_DATE and CURRENT_DATE + 1
GROUP by ec.ID_Equipo_Detalle

do this

LEFT JOIN elecciones_contactos as ec
 on ec.ID_Equipo_Detalle = eq.ID_Equipos_Detalle
   and ec.FechaAlta BETWEEN CURRENT_DATE and CURRENT_DATE + 1
GROUP by e.ID_Equipos

Also notice that I have moved the group by from ec.ID_Equipo_Detalle to e.ID_Equipos

Here is a db-fiddle for demonstration.

MySQL count return 0 instead of no records

I ended up making this work by joining in a separate calendar table similar to what was suggested in this post: SQL left joining multiple tables

Here is the full query:

"SELECT 
  allRecords.date,
  allRecords.Name,
  ( SELECT 
      COUNT(statuses.status_id)
    FROM statuses, builds, calendar
    WHERE builds.status_id = statuses.status_id
      AND FROM_UNIXTIME(builds.created, '%Y-%m-%d') = calendar.date
      AND calendar.date = allRecords.date
      AND statuses.name = allRecords.Name
  ) as Count

FROM 
  ( SELECT calendar.date,
      statuses.status_id,
      statuses.name
    FROM
      calendar,
      statuses
    ORDER BY
      calendar.date,
      statuses.name  ) allRecords

LEFT JOIN builds
  ON builds.status_id =  allRecords.status_id

LEFT JOIN statuses
  ON builds.status_id = statuses.status_id

WHERE allRecords.date BETWEEN 
  (SELECT MIN(FROM_UNIXTIME(builds.created, '%Y-%m-%d')) FROM builds) 
AND 
  (SELECT MAX(FROM_UNIXTIME(builds.created, '%Y-%m-%d')) FROM builds)

GROUP BY
  allRecords.date,
  allRecords.Name

ORDER BY 
  allRecords.date,
  CASE allRecords.Name
    WHEN 'Stable' THEN 1
    WHEN 'Unstable' THEN 2
    WHEN 'Failed' THEN 3
  ELSE 100 END";

---

Related Topics