There are two tables. Tables A has following structure ID Flag Name 1X 1 Y 2Y 0 Null 3Z 1 Null 4A 1 Y Table B has the following structure B_ID City State 1X Y Null 2Y Null Null 3Z Null Y 4A Y Y I want to get the count of all the IDs and the count of IDs

Query to find the count of total IDs, and IDs having null in any column and the percentage of the two (i.e. Count of ID having null /total ID count)

There are two tables. Tables A has following structure

ID	Flag	Name
1X	1	Y
2Y	0	Null
3Z	1	Null
4A	1	Y

Table B has the following structure

B_ID	City	State
1X	Y	Null
2Y	Null	Null
3Z	Null	Y
4A	Y	Y

I want to get the count of all the IDs and the count of IDs that have Null in any of the columns (name, city, state), for e.g from the tables above only the ID 4A has non null value in all the three columns across both the tables, so the output should be like

Total_Count	Ids having null	Percentage missing
4	3	0.75%

Total_count is 4 as there are total of four IDs, ID having NULL is 3 because there are 3 IDs that have null in any of the three columns (viz. name,city,state), and Percentage missing is just IDs having null / Total_Count.

I tried using a query along the following lines

select (count/total) * 100 pct, count,total 
from (select sum(count) count 
      from(select count(*) count from tableA T1
      where T1.name is null
      union all
      select count(*) count from tableA T1
      join tableB T2 on T1.ID = T2.B_ID 
      where T2.city is null
      union all
      select count(*) count from tableA T1
      join tableB T2 on T1.ID = T2.B_ID 
      where T2.state is null)),
select count(ID) total from tableA);

But the query is not returning the desired output, can you please suggest me a better way? Thank You

Answer

Use conditional aggregation:

SELECT COUNT(*) Total_Count,    
       COUNT(CASE WHEN t1.Name IS NULL OR t2.City IS NULL OR t2.State IS NULL THEN 1 END) Ids_having_null,  
       AVG(CASE WHEN COALESCE(t1.Name, t2.City, t2.State) IS NOT NULL THEN 1 ELSE 0 END) Percentage_missing
FROM Table1 t1 INNER JOIN Table2 t2
ON t2.B_ID = t1.ID;

See the demo.

Advertisement

Answer