Unable to use exact_count_distinct BigQuery aggregate function

Question

I am getting Resources exceeded during query execution error when I execute following query:

SELECT EXACT_COUNT_DISTINCT( id ) FROM [bigquery-public-data:github_repos.contents]

I used EXACT_COUNT_DISTINCT aggregate function as COUNT([DISTINCT]) function gives only a statistical approximation.

Why are you doing that? There's only one row per id - so the number of rows in the table is that count. — Felipe Hoffa
Hey I have a BigQuery table in my own GCP project which has duplicate column values. So I was trying to replicate the problem with the same query in a public dataset. — abhishek jha

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-08-14T18:47:02

try below

for BigQuery Legacy SQL

SELECT COUNT(1) AS cnt FROM (
  SELECT id 
  FROM [bigquery-public-data:github_repos.contents]
  GROUP BY id
)

for BigQuery Standard SQL

SELECT COUNT(DISTINCT id) as cnt
FROM `bigquery-public-data.github_repos.contents`