How to insert a zero when COUNT returns null? MySQL

Question

I have the following in a trigger and want it to insert zero when NULL is returned. I have read various posts here on Stackoverflow and other sites over the past two days but have been unable to achieve what I want...and am afraid that it may not be possible...? This query returns all users from MCVMoodle.mdl_facetoface_signups.userid with count values for grade (that meet the WHERE conditions) with count values of 1 or more but nothing for 0/null. I understand the difficulty of working with no result/null but is there anyway to get a zero inserted when the count is 0/null?

INSERT INTO MCVMoodle.mdl_user_info_data_f2fskipcount (userid, fieldid, datab)

SELECT MCVMoodle.mdl_facetoface_signups.userid as 'userid', 4 as 'fieldid',
      COUNT(MCVMoodle.mdl_facetoface_signups_status.grade) as 'datab'
        FROM
            MCVMoodle.mdl_facetoface_signups
        LEFT JOIN
            MCVMoodle.mdl_facetoface_signups_status on MCVMoodle.mdl_facetoface_signups.id = MCVMoodle.mdl_facetoface_signups_status.signupid
       WHERE
    MCVMoodle.mdl_facetoface_signups_status.statuscode = 80
      AND  MCVMoodle.mdl_facetoface_signups_status.superceded = 0
      AND  MCVMoodle.mdl_facetoface_signups_status.grade = 0
       GROUP BY
        MCVMoodle.mdl_facetoface_signups.userid;

Below is the query that finally worked for me. Thanks to Gordon for crucial help on this.

INSERT INTO MCVMoodle.mdl_user_info_data_f2fskipcount (userid, fieldid, datab)

SELECT MCVMoodle.mdl_facetoface_signups.userid as 'userid', 4 as 'fieldid',
             COUNT(MCVMoodle.mdl_facetoface_signups_status.grade) as 'datab'
      FROM MCVMoodle.mdl_facetoface_signups LEFT JOIN
           MCVMoodle.mdl_facetoface_signups_status
           on MCVMoodle.mdl_facetoface_signups.id = MCVMoodle.mdl_facetoface_signups_status.signupid and
              MCVMoodle.mdl_facetoface_signups_status.statuscode = 80 AND
              MCVMoodle.mdl_facetoface_signups_status.superceded = 0 AND
              MCVMoodle.mdl_facetoface_signups_status.grade = 0
      GROUP BY MCVMoodle.mdl_facetoface_signups.userid
      ON DUPLICATE KEY UPDATE
       datab=VALUES(datab);

possible duplicate of [How to return 0 instead of null when using COUNT in MySQL](http://stackoverflow.com/questions/7970824/how-to-return-0-instead-of-null-when-using-count-in-mysql) — Thilo, Sep 10 '13 at 02:20
Or maybe not. If your count return NULL, then check the linked question. If no row is returned at all, it is something else (probably need to move criteria from WHERE to ON). Your question is a bit vague on that point. — Thilo, Sep 10 '13 at 02:22
Thilo, thanks, actually I did read through the post you linked and tried the various suggestions listed there but couldn't get any of them to work. Sorry about my comment being vague...that is most likely because of my lack of knowledge. I guess what I am wanting is a zero when no rows exist...if that's possible? What I mean is, when there are no rows that meet the criteria in the WHERE clause, I would to insert 'userid', 4, 0 (Does that make a little more sense?) Thanks. — Jason Hollowell, Sep 10 '13 at 07:06

Gordon Linoff · Accepted Answer · 2013-09-10T13:36:03.790

If I understand correctly, you want to insert everything from the query. But if the query is empty, then insert a row of all NULLs. The following does this, but not in MySQL:

with cte as (
      SELECT MCVMoodle.mdl_facetoface_signups.userid as 'userid', 4 as 'fieldid',
             COUNT(MCVMoodle.mdl_facetoface_signups_status.grade) as 'datab'
      FROM MCVMoodle.mdl_facetoface_signups LEFT JOIN
           MCVMoodle.mdl_facetoface_signups_status
           on MCVMoodle.mdl_facetoface_signups.id = MCVMoodle.mdl_facetoface_signups_status.signupid
      WHERE MCVMoodle.mdl_facetoface_signups_status.statuscode = 80 AND
            MCVMoodle.mdl_facetoface_signups_status.superceded = 0 AND
            MCVMoodle.mdl_facetoface_signups_status.grade = 0
      GROUP BY MCVMoodle.mdl_facetoface_signups.userid
     )
INSERT INTO MCVMoodle.mdl_user_info_data_f2fskipcount (userid, fieldid, datab)
    select *
    from cte
    union all
    select NULL, NULL, NULL
    where not exists (select * from CTE);

The MySQL version is more complicated:

INSERT INTO MCVMoodle.mdl_user_info_data_f2fskipcount (userid, fieldid, datab)
    select *
    from (SELECT MCVMoodle.mdl_facetoface_signups.userid as 'userid', 4 as 'fieldid',
                 COUNT(MCVMoodle.mdl_facetoface_signups_status.grade) as 'datab'
          FROM MCVMoodle.mdl_facetoface_signups LEFT JOIN
               MCVMoodle.mdl_facetoface_signups_status
               on MCVMoodle.mdl_facetoface_signups.id = MCVMoodle.mdl_facetoface_signups_status.signupid
          WHERE MCVMoodle.mdl_facetoface_signups_status.statuscode = 80 AND
                MCVMoodle.mdl_facetoface_signups_status.superceded = 0 AND
                MCVMoodle.mdl_facetoface_signups_status.grade = 0
          GROUP BY MCVMoodle.mdl_facetoface_signups.userid
         ) cte
    union all
    select NULL, NULL, NULL
    where not exists (SELECT MCVMoodle.mdl_facetoface_signups.userid as 'userid', 4 as 'fieldid',
                             COUNT(MCVMoodle.mdl_facetoface_signups_status.grade) as 'datab'
                      FROM MCVMoodle.mdl_facetoface_signups LEFT JOIN
                           MCVMoodle.mdl_facetoface_signups_status
                           on MCVMoodle.mdl_facetoface_signups.id = MCVMoodle.mdl_facetoface_signups_status.signupid
                      WHERE MCVMoodle.mdl_facetoface_signups_status.statuscode = 80 AND
                            MCVMoodle.mdl_facetoface_signups_status.superceded = 0 AND
                            MCVMoodle.mdl_facetoface_signups_status.grade = 0
                      GROUP BY MCVMoodle.mdl_facetoface_signups.userid
            );

EDIT:

It occurs to me that you might just want to include all combinations from the query, even those that are eliminated by the where clause. If so, then move the conditions to the on clause:

with cte as (
      SELECT MCVMoodle.mdl_facetoface_signups.userid as 'userid', 4 as 'fieldid',
             COUNT(MCVMoodle.mdl_facetoface_signups_status.grade) as 'datab'
      FROM MCVMoodle.mdl_facetoface_signups LEFT JOIN
           MCVMoodle.mdl_facetoface_signups_status
           on MCVMoodle.mdl_facetoface_signups.id = MCVMoodle.mdl_facetoface_signups_status.signupid and
              MCVMoodle.mdl_facetoface_signups_status.statuscode = 80 AND
              MCVMoodle.mdl_facetoface_signups_status.superceded = 0 AND
              MCVMoodle.mdl_facetoface_signups_status.grade = 0
      GROUP BY MCVMoodle.mdl_facetoface_signups.userid
     )
INSERT INTO MCVMoodle.mdl_user_info_data_f2fskipcount (userid, fieldid, datab)
    select *
    from cte;

Gordon, thank you for your help. Actually, what I want is to insert the 'userid' and a 4 for 'fieldid' and the count for 'datab' given the conditions. The query I posted works fine except when it results in no results (or null...I guess). From the perspective of my application, no results needs to equal a zero but given the limitations of mysql (or hopefully just my knowledge) I can't seem to figure out how to get a zero inserted when the query ends up with no results for a particular 'userid'. Does that make sense? — Jason Hollowell, Sep 10 '13 at 05:07
@JasonHollowell . . . The first version inserts a row when no rows would be inserted from your subquery. The second inserts rows for all userids, even if the count would be 0. — Gordon Linoff, Sep 10 '13 at 11:25
Gordon, thanks for you assistance. I just tried this and get a syntax error at "with cte"...I'm not sure what I'm doing wrong...? I am using MySQL 5.1.69...not sure if that matters but... — Jason Hollowell, Sep 10 '13 at 12:28
@JasonHollowell . . . Ouch. I missed that this was tagged MySQL. — Gordon Linoff, Sep 10 '13 at 13:33
Gordon, many thanks for your continued support. I just tried the MySQL version that you posted. I'm getting a syntax error at line 15 (WHERE NOT EXISTS). And, an aside, I'm curious about the INSERT NULL NULL NULL line. Will that result in three zeros? What I'm hoping to achieve is a userid (e.g.301467), the 4 for fieldid and then a 0 for dabab when the count returns nothing....Maybe that is what the query you submitted for me will achieve and I'm just not following the logic... — Jason Hollowell, Sep 10 '13 at 23:39
@JasonHollowell . . . No. That is closer to the last query in the answer. Replace the CTE with the full query (it is just a convenience in that case). — Gordon Linoff, Sep 10 '13 at 23:40
Gordon, brilliant! Thanks. I'm going to post the query that finally got me what I wanted in the hopes that it might help others...I saw quite a few posts related to returning zero when the count result was null but none were quite sufficient enough to get me where I wanted to be. Thanks so much for your help! — Jason Hollowell, Sep 11 '13 at 00:44
Gordon, sorry to keep bugging you but as I experiment to make sure everything works in my application I notice that my trigger is not quite working correctly. I have it set as an AFTER INSERT trigger but it doesn't count the line that is being entered. The count that should change when a new record is entered doesn't change until another insert (a second insert) is made. It seems that even though it's an AFTER INSERT, it isn't being counted. — Jason Hollowell, Sep 11 '13 at 01:33
@JasonHollowell . . . I don't know the answer to that question. Perhaps you should ask another question and include the code for the trigger. — Gordon Linoff, Sep 11 '13 at 01:35

How to insert a zero when COUNT returns null? MySQL

1 Answers1