1

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

Mawu Ena
  • 11
  • 3

1 Answers1

2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786