Is there a way to construct SQL statement to return desired value only if there is a single row found, and return 0 otherwise?
One way is using COUNT and MIN/MAX:

SELECT
    CASE WHEN COUNT(*) &gt; 1 THEN 0 ELSE MIN(contact_id) END
FROM
    contacts
WHERE
    customer_office_id = [int]
GROUP BY
    customer_office_id;


db&lt;>fiddle here

To cover the case when there are no rows returned you can use:

SELECT
    COALESCE((SELECT
                  CASE WHEN COUNT(*) &gt; 1 THEN 0 ELSE MIN(contact_id) END contact_id
              FROM
                  contacts
              WHERE
                  customer_office_id = [INT]
              GROUP BY
                  customer_office_id), 0) contact_id;


db&lt;>fiddle here