Replace A No-Result query with Zero

Transact-SQL
1

Hi all, I'm new to this forum.
I want to replace whenever a query does not return a result with zero instead.
I have over 10 tables that I want the count of some values for each date in the tables but for the purpose of this request, I will limit to only 2 tables and 2 dates.

See the query below

SELECT a.* 
FROM 
    (SELECT 
        'count_male_account' AS count_name, 
        date, 
        count(*) AS actual_count 
    FROM gender_table 
    WHERE date IN ('2021-10-09', '2021-10-10') -- selected only 2 from over 4 months worth
    AND gender = 'male' 
    GROUP BY date

    UNION 

    SELECT 
        'count_female_account' AS count_name, 
        date, 
        count(*) AS actual_count 
    FROM gender_table 
    WHERE date IN ('2021-10-09', '2021-10-10') -- selected only 2 from over 4 months worth
    AND gender = 'female' 
    GROUP BY date 

    UNION 

    SELECT 
        'count_null_address' AS count_name, 
        date, 
        count(*) AS actual_count 
    FROM address_table 
    WHERE date IN ('2021-10-09', '2021-10-10') -- selected only 2 from over 4 months worth
    AND address is NULL 
    GROUP BY date 

    UNION 

    SELECT 
        'count_public_phone_number' AS count_name, 
        date, 
        count(*) AS actual_count 
    FROM address_table 
    WHERE date IN ('2021-10-09', '2021-10-10') -- selected only 2 from over 4 months worth
    AND phone_cat = 'public' 
    GROUP BY date) a
ORDER BY date desc, count_name;

Output I'm getting from the query above is

+-------------------------+----------+------------+
|count_name               |date      |actual_count|
+-------------------------+----------+------------+
|count_female_account     |2021-10-10|3217        |
|count_male_account       |2021-10-10|306         |
|count_public_phone_number|2021-10-10|372         |
|count_female_account     |2021-10-09|3787        |
|count_male_account       |2021-10-09|377         |
|count_null_address       |2021-10-09|5           |
+-------------------------+----------+------------+

But in my desired output, I want all rows to be there even if there are no count. For example, for date = 2021-10-10 , there's no address with null value in the address column, hence instead of the count_null_address to be missing in my result, I want it's actual_count = 0 . Same goes for date = 2021-10-09 where there are no public phone number. See my desired output below:

+-------------------------+----------+------------+
|count_name               |date      |actual_count|
+-------------------------+----------+------------+
|count_female_account     |2021-10-10|3217        |
|count_male_account       |2021-10-10|306         |
|count_null_address       |2021-10-10|0           |
|count_public_phone_number|2021-10-10|372         |
|count_female_account     |2021-10-09|3787        |
|count_male_account       |2021-10-09|377         |
|count_null_address       |2021-10-09|5           |
|count_public_phone_number|2021-10-09|0           |
+-------------------------+----------+------------+
2

You should place the count in an extra subquery so you always have a date at least, for example:

 SELECT 
        'count_null_address' AS count_name, 
        date, 
        ISNULL((SELECT count(*) 
    FROM address_table 
    WHERE date IN ('2021-10-09', '2021-10-10') -- selected only 2 from over 4 months worth
    AND address is NULL 
    GROUP BY date),0)  AS actual_count 
FROM address_table
WHERE date IN ('2021-10-09', '2021-10-10')
3

i dont know if this is relevant you what you are doing ?

but left join .. will put null where there is no data
replace null with zero

image
image711×719 9.35 KB

4

Thanks for the suggestion. I tried the ISNULL function as you suggested but it was given me an error: Invalid number of arguments for function isnull. Expected: 1; Found: 2; So I changed it to IFNULL, yet because there are no address that were NULL on 2021-10-10, it was still omitted in the result.

SELECT 
    'count_null_address' AS count_name, 
    date, 
    ISNULL((SELECT count(*) 
FROM address_table 
WHERE date IN ('2021-10-09', '2021-10-10') -- selected only 2 from over 4 months worth
AND address is NULL 
GROUP BY date),0)  AS actual_count 
FROM address_table
WHERE date IN ('2021-10-09', '2021-10-10')

Result below

+-------------------------+----------+------------+
|count_name               |date      |actual_count|
+-------------------------+----------+------------+
|count_null_address       |2021-10-09|5           |
+-------------------------+----------+------------+
5

if you do left join .. nulls comes

please give me some time
i can take a closer look with Sample Data

:+1: :+1:

6

hi i took a closer look

please use UNION all instead of UNION


select 1
   union all 
select 2
   union all 
select 3
7

Thanks for helping out. I looked at UNION ALL and it's still not giving me what I want. To make the requirement easy to understand. Let's say I have a table table_a and I want to query the table with a clause WHERE 1 = 2

SELECT 
   'where_1equals_2' as count_name, 
   date, 
   count(*) as actual_count 
FROM table_a 
WHERE 1 = 2 
AND date = '2022-01-30';

Ideally this query will give empty result becaue there 's no way where 1 = 2.

    +---------------------+----------+------------+
    |count_name           |date      |actual_count|
    +---------------------+----------+------------+
    |                     |          |            |
    +---------------------+----------+------------+

But instead of empty result, I want the count = 0.

+---------------------+----------+------------+
|count_name           |date      |actual_count|
+---------------------+----------+------------+
|where_1equals_2      |2022-01-30|0           |
+---------------------+----------+------------+

That's my requirement.

8

one way

SELECT    'where_1equals_2' as count_name,    date,    count(*) as actual_count 
FROM table_a  WHERE 1 = 2  AND date = '2022-01-30'
   union 
SELECT    'where_1equals_2' as count_name,    date,   0  as actual_count
9

image

11

you can google search

image
image719×685 40.8 KB