CODEWITHSUNDEEP
sqlgoogle-bigquerylegacy-sql
Mawu Ena
Mawu Ena

Reputation: 21

BigQuery - Get percentage of null values in column grouped by another

I have a table like

store_code country_code timestamp
null FR 1234567890
123 FR 1234567890
456 GB 1234567890
789 GB 1234567890

I would like to get

pct_null_store_code country_code
0.5 FR
0.0 GB

I know how to do it with a WHERE clause as in

SELECT SAFE_DIVIDE(COUNTIF(store_code IS NULL), COUNT(1)) as pct_null_store_code
FROM table
WHERE country_code = 'XX'

But I would like to only have to exec 1 query and get the results for each country_code

Upvotes: 2

Views: 2214

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

One method just uses avg():

SELECT country_code, 
       AVG(case when store_code IS NULL then 1.0 else 0 end) as null_ratio
FROM table
GROUP BY country_code;

If you want to do the count and divide by the total, then use COUNTIF():

SELECT country_code, 
       COUNTIF(store_code IS NULL) * 1.0 / COUNT(*) as null_ratio
FROM table
GROUP BY country_code;

Upvotes: 2

Related Questions