What you will be able to do
- Explain how an aggregate function collapses the rows of each group, and how DISTINCT and FILTER change what reaches it
- Predict the result of count(*), count(expr), count(DISTINCT expr) and multi-column count when NULLs are present
- Choose approx_count_distinct over count(DISTINCT ...) when an estimate within a known error is acceptable, and describe its accuracy and relativeSD parameter
Key concept
Aggregate function over a group — An aggregate function takes all the rows of one group and returns a single value for that group. GROUP BY decides what the groups are. DISTINCT and FILTER decide which input rows the function sees before it does its calculation.
1.What an aggregate works on: groups, DISTINCT and FILTER
Every aggregate in Databricks SQL (count, avg, median, stddev and the rest) answers its question once per group. The GROUP BY clause is what creates those groups. In the reference's words, it is used 'to group the rows based on a set of specified grouping expressions' and then compute aggregations over each group. Without GROUP BY, the whole result set is a single group, and you get one row back. The reference calls this a global aggregation.
On Databricks SQL and Databricks Runtime 12.2 LTS and above you can write GROUP BY ALL instead of listing the grouping columns. It is 'a shorthand notation to add all SELECT-list expressions not containing aggregate functions as group_expressions.' So if your SELECT list holds a region column and count(*), GROUP BY ALL groups by region. If every SELECT-list expression is an aggregate, GROUP BY ALL is equivalent to leaving out the clause, which gives a global aggregation. If the generated clause isn't well-formed, Databricks raises UNRESOLVED_ALL_IN_GROUP_BY or MISSING_AGGREGATION.
Inside a group, two modifiers control what the function actually receives. DISTINCT 'removes duplicates in input rows before they are passed to aggregate functions', so the function only sees each value once. FILTER (WHERE cond) works per function: 'only the matching rows are passed to that function.' This means one SELECT can return count(*) next to count(*) FILTER (WHERE status = 'late'), and the WHERE clause for the whole query still applies to both. All the functions in this lesson accept FILTER, and most accept DISTINCT. The rest of the lesson is about what each function does with the rows it gets.
Checkpoint 1 of 6· Check yourself
You need total orders and late orders per customer in a single pass. Which approach does the GROUP BY reference describe for this?
A FILTER clause restricts only the aggregate it is attached to, so an unfiltered count can sit beside a filtered one. A WHERE clause would remove the other rows from both counts.
“When a FILTER clause is attached to an aggregate function, only the matching rows are passed to that function.”Source: docs.databricks.com
Sources1
2.count(*), count(expr) and count(DISTINCT expr)
count returns a BIGINT. It looks simple, but the different forms treat NULL differently, and that difference is what exam questions test. The reference shows all of them on the same four values: NULL, 5, 5 and 20. Try to predict each result before reading the next paragraph.
SELECT count(*) FROM VALUES (NULL), (5), (5), (20) AS tab(col);SELECT count(col) FROM VALUES (NULL), (5), (5), (20) AS tab(col);count(*) returns 4 and count(1) also returns 4. Both count rows, and the NULL row is still a row. count(col) returns 3, because it skips the row where col is NULL.
Here is the rule behind those results. With *, count 'counts all rows in the group.' With an expression, it counts rows 'for which all exprN are not NULL.' The word 'all' matters when you pass more than one column. count(col1, col2) only counts a row when every listed column is non-NULL. In the reference's seven-row example, the rows (NULL, NULL), (5, NULL) and (NULL, 2) are skipped, so the answer is 4. Adding FILTER narrows things further: count(col) FILTER(WHERE col < 10) on the original data returns 2, the two 5s.
DISTINCT removes duplicates before counting, and NULL still isn't counted: the function 'returns the number of unique values which do not contain NULL.' With the values NULL, 5, 5 and 10, count(DISTINCT col) is 2. The two 5s count once, the 10 counts once, and the NULL is ignored. The opposite keyword is ALL, which is the default behaviour. With *, ALL includes rows that contain NULL.
Checkpoint 2 of 6· Fill the gap
This query returns 2: the unique non-NULL values 5 and 10. Which keyword completes it?
SELECT count( ? col) FROM VALUES (NULL), (5), (5), (10) AS tab(col);count(DISTINCT col) removes the duplicate 5 and skips NULL, which leaves 2. With ALL the result would be 3, because every non-NULL value is counted. UNIQUE is not a count modifier.
Source: docs.databricks.comCheckpoint 3 of 6· Match them up
Match each form of count to what it counts
Tap a term, then the definition that fits it.
Only the * form counts NULL rows. An expression form needs every listed expression to be non-NULL, and DISTINCT also removes duplicates.
“expr: Counts all rows for which all exprN are not NULL.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
An analyst runs `SELECT COUNT(*), COUNT(email) FROM customers;` against a table where 200 rows have a populated `email` column and 30 rows have `email` set to `NULL`, out of 230 total rows. What do the two results represent?
Correct answer: A — `COUNT(*)` returns 230 because it counts every row regardless of NULLs, while `COUNT(email)` returns 200 because it skips rows where `email` is NULL
- A. This is correct because `COUNT(*)` counts every row in the result set including rows with NULLs, while `COUNT(column)` only counts rows where that specific expression is non-NULL, so `COUNT(email)` excludes the 30 NULL rows and returns 200.
- B. This is incorrect because `COUNT(column)` is defined to skip NULL values for that column; it does not simply mirror `COUNT(*)`, so the two counts diverge whenever the column has NULLs.
- C. This is incorrect and reverses the behavior: `COUNT(*)` never excludes rows for NULLs in any column, and `COUNT(email)` is the one that drops rows where `email` itself is NULL.
- D. This is incorrect because Databricks SQL fully supports listing `COUNT(*)` alongside `COUNT(column)` in the same SELECT clause; there is no restriction that forces only one of them to execute.
Sources2
3.approx_count_distinct: trading exactness for speed
On very large tables, an exact count(DISTINCT user_id) can be expensive. Databricks notes that 'using approximation for aggregates can accelerate query processing and reduce costs when you don't require precise results', and lists approx_count_distinct as one of the native approximation functions, next to approx_percentile and approx_top_k.
SELECT approx_count_distinct(col1) FROM VALUES (1), (1), (2), (2), (3) tab(col1);The function 'uses the dense version of the HyperLogLog++ (HLL++) algorithm', which is a cardinality estimation algorithm. The full signature is approx_count_distinct(expr[, relativeSD]) [FILTER (WHERE cond)]. expr can be any type for which equivalence is defined. Results are accurate within a default value of 5%. That figure comes from the maximum relative standard deviation, and you can change it with the optional second argument: 'relativeSD: Defines the maximum relative standard deviation allowed.' The return type is BIGINT, the same as count. Like count, it accepts FILTER and can also be used as a window function with OVER.
| Aspect | count(DISTINCT expr) | approx_count_distinct(expr) |
|---|---|---|
| Result | Exact number of unique non-NULL values | Estimated number of distinct values |
| Accuracy control | None needed | relativeSD; default accuracy within 5% |
| Method | Removes duplicates, then counts | HyperLogLog++ (dense version) |
| Return type | BIGINT | BIGINT |
There are two other ways to approximate, and they behave differently. Sampling with TABLESAMPLE lets you 'generate a random sample from a dataset and calculate approximate aggregates.' LIMIT is not a sample. It gives a quick look at the data 'but does not introduce randomness', and it doesn't guarantee the rows come from across the whole dataset. So an aggregate computed over LIMIT rows tells you about those particular rows, not about the table.
Checkpoint 5 of 6· Check yourself
A dashboard tile shows daily unique visitors over billions of events. Analysts accept a small error if the tile loads faster. What should the query use, and what accuracy can they expect by default?
approx_count_distinct uses HLL++ to estimate distinct values. By default the estimate is within 5%, and you can tune this with relativeSD. A BIGINT return type does not mean the result is exact, and LIMIT gives no statistical guarantee.
“Results are accurate within a default value of 5%”Source: docs.databricks.com
Checkpoint 6 of 6· Exam question
A marketing analyst needs the exact number of unique `customer_id` values that placed at least one order in the `orders` table, and the result will be used in a compliance report where precision matters. Which query correctly returns that exact unique count?
Correct answer: A — `SELECT COUNT(DISTINCT customer_id) FROM orders;`, since DISTINCT deduplicates the values before COUNT tallies them, giving an exact unique total
- A. This is correct because `COUNT(DISTINCT customer_id)` deduplicates the column values before counting, returning the exact number of unique customers, unlike approximate cardinality functions.
- B. This is incorrect because `approx_count_distinct` trades exactness for speed and memory efficiency using the HyperLogLog++ algorithm, returning an estimate within roughly 5% of the true value by default, which does not satisfy a compliance report that needs an exact figure.
- C. This is incorrect because grouping by `customer_id` without an outer aggregation returns one row per distinct customer with their per-group count, not a single overall total of unique customers.
- D. This is incorrect because summing distinct customer identifiers adds the numeric ID values together, which produces a meaningless arithmetic total rather than a count of how many unique customers exist.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.count(col) and count(*) always return the same number.Why is that wrong?
count(*) counts every row, including NULL rows. count(col) only counts rows where col is not NULL. For the values NULL, 5, 5, 20 they return 4 and 3.
2.approx_count_distinct gives the exact distinct count, just faster.Why is that wrong?
It returns an HLL++ estimate. By default the result is within 5%, and relativeSD controls the error bound. Use count(DISTINCT ...) when you need an exact figure.
Covered in approx_count_distinct: trading exactness for speed
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-groupbyOfficial docs
“A shorthand notation to add all SELECT-list expressions not containing aggregate functions as group_expressions.”
↩︎ What an aggregate works on: groups, DISTINCT and FILTER“DISTINCT Removes duplicates in input rows before they are passed to aggregate functions.”
↩︎ What an aggregate works on: groups, DISTINCT and FILTER“If no such expression exist GROUP BY ALL is equivalent to omitting the GROUP BY clause which results in a global aggregation.”
↩︎ Prediction“When a FILTER clause is attached to an aggregate function, only the matching rows are passed to that function.”
↩︎ Checkpoint - 2.
“If DISTINCT is specified then the function returns the number of unique values which do not contain NULL.”
↩︎ count(*), count(expr) and count(DISTINCT expr)“*: Counts all rows in the group.”
↩︎ count(*), count(expr) and count(DISTINCT expr)“Returns the number of retrieved rows in a group.”
↩︎ Key concept“expr: Counts all rows for which all exprN are not NULL.”
↩︎ Exam trap 1“expr: Counts all rows for which all exprN are not NULL.”
↩︎ Checkpoint - 3.
“The implementation uses the dense version of the HyperLogLog++ (HLL++) algorithm”
↩︎ approx_count_distinct: trading exactness for speed“Returns the estimated number of distinct values in expr within the group.”
↩︎ Exam trap 2“Returns the estimated number of distinct values in expr within the group.”
↩︎ Prediction“Results are accurate within a default value of 5%”
↩︎ Checkpoint - 4.
“using approximation for aggregates can accelerate query processing and reduce costs when you don't require precise results.”
↩︎ approx_count_distinct: trading exactness for speed“Using LIMIT statements is sometimes good enough for getting a quick snapshot of data, but does not introduce randomness”
↩︎ approx_count_distinct: trading exactness for speed