Sqlite: get max value set to zero when row doesn't exist or is null

Question

I have this table equip_info:

   equip_id | fam_id | asset_no
  ----------|--------|-----------
    1       | 1      |  1
    2       | 1      |  2

I would like to insert a new equip_id from a different fam_id and to get the asset_no incremented.

If I do so:

INSERT INTO equip_info (fam_id, asset_no)
VALUES (1, (SELECT MAX(asset_no) + 1 FROM equip_info WHERE fam_id = 1))

I get bellow result:

   equip_id | fam_id | asset_no
  ----------|--------|-----------
    1       | 1      |  1
    2       | 1      |  2
    3       | 1      |  3

But, if I do so:

INSERT INTO equip_info (fam_id, asset_no)
VALUES (2, (SELECT MAX(asset_no) + 1 FROM equip_info WHERE fam_id = 2))

it said: NOT NULL constraint failed: equip_info.asset_no

If the fam_id = 2 doesn't exist how can I get increment to 1 instead?

I would like to get this result:

   equip_id | fam_id | asset_no
  ----------|--------|-----------
    1       | 1      |  1
    2       | 1      |  2
    3       | 1      |  3
    4       | 2      |  1

Remark: equip_id is primary key, autoincremented

Answer 1

MAX(asset_no) is NULL when there is no matching row.

To replace NULL with some other value, use IFNULL, i.e., replace MAX(asset_no) with IFNULL(MAX(asset_no), 0).