Google BigQuery Legacy SQL calculates an exact count_distinct if there are fewer than 1,000 distinct values, but performs an approximation for larger numbers of distinct values. The approximate count is faster, but typically has an error of a few percent.
If you want BigQuery Legacy SQL to calculate an exact count_distinct for more than 1,000 distinct values, you can set that amount with the approximate_threshold parameter.
Examples
Return an exact count_distinct for up to 100,000 distinct values:
measure: unique_count {
type: count_distinct
approximate_threshold: 100000 # default value is 1000
sql: ${id} ;;
}
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-14 UTC."],[],[],null,["# approximate_threshold\n\n\u003cbr /\u003e\n\nUsage\n-----\n\n```\nview: view_name {\n measure: field_name {\n approximate_threshold: 100000\n }\n}\n```\n\nDefinition\n----------\n\nGoogle BigQuery Legacy SQL calculates an exact `count_distinct` if there are fewer than 1,000 distinct values, but performs an approximation for larger numbers of distinct values. The approximate count is faster, but typically has an error of a few percent.\n\nIf you want BigQuery Legacy SQL to calculate an exact `count_distinct` for more than 1,000 distinct values, you can set that amount with the `approximate_threshold` parameter.\n\nExamples\n--------\n\nReturn an exact `count_distinct` for up to 100,000 distinct values: \n\n measure: unique_count {\n type: count_distinct\n approximate_threshold: 100000 # default value is 1000\n sql: ${id} ;;\n }"]]
