Is there a way to construct SQL statement to return desired value only if there is a single row found, and return 0 otherwise? Consider below:

    SELECT 
        customer.contact.id
    FROM customer.contact
    WHERE
        customer_office_id = [INTEGER]


If only a single row is returned, I want the result set to return the customer.contact.id, but if 0 or more than 1 rows are returned, I want the SQL statement to return 0 (that is, despite that there are multiple rows, or no rows, I want SQL to return a single row with a value that is 0)

Note - if there is a special SQL case for when only 0 or 1 rows returned are possible, I'd be interested in that as well, i.e if there are no more than 1 rows possible given the data.
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