BigQuery – All results zero when subquery returns no results

January 22, 2024

I am trying to form a query that will count distinct rows from multiple tables based on a variable input by the user.

However, one or more tables may return zero results for the {{Customer}} variable passed,

When I run the query passing a {{Customer}} that has data in all tables, I get the expected count of all tables; however, if one of the tables returns zero, then all results are zero.

I have tried splitting it into separate counts to just get three results, but again, as soon as one result is zero, all results are zero.

Would someone be able to offer advice on how to ignore the subquery when zero results are returned or a better way to get a count of distinct results in multiple tables?

    SELECT COUNT (DISTINCT x.LicenseKey) + COUNT (DISTINCT y.LicenseKey) + COUNT (DISTINCT z.LicenseKey) AS Number_Of_Licenses
FROM (
    SELECT LicenseKey
    FROM `table_1` 
    WHERE Customer LIKE {{Customer}}
    AND Trial = false
    ) as x,
    (SELECT LicenseKey
    FROM `table_2`
    WHERE Customer LIKE {{Customer}}
    AND Trial = false
    ) as y,
    (SELECT LicenseKey
    FROM `table_3`
    WHERE Customer LIKE {{Customer}}
    AND Trial = false
    ) as z

Any help will be greatly appreciated. 🙂

>Solution :

As all count queires would return a scalar value, you can use them directly

SELECT 
(
SELECT COUNT(DISTINCT  LicenseKey)
FROM `table_1` 
WHERE Customer LIKE {{Customer}}
AND Trial = false
) +
(SELECT COUNT(DISTINCT  LicenseKey)
FROM `table_2`
WHERE Customer LIKE {{Customer}}
AND Trial = false
) +
(SELECT COUNT(DISTINCT  LicenseKey)
FROM `table_3`
WHERE Customer LIKE {{Customer}}
AND Trial = false
) as COUNT_LicenseKey