What you will be able to do
- Predict how GROUP BY collapses rows and which columns may appear beside an aggregate in the SELECT list
- Choose between count(*), count(expr), count(DISTINCT expr) and FILTER, and predict how each one treats NULL
- Decide when approx_count_distinct should replace an exact COUNT(DISTINCT), and what its relativeSD parameter controls
- Compute mean, median and sample standard deviation in Databricks SQL, and predict their results for NULL, empty and single-row groups
Key concept
Aggregate over a group — An aggregate function reads every row in a group and returns a single value for it. Without GROUP BY, the whole table is one group. With GROUP BY, the query returns one output row for each distinct combination of the grouping expressions.
1.How GROUP BY collapses rows into groups
Most analysis questions are aggregate questions: how many orders, how many distinct customers, the average basket per region. An aggregate function such as count, sum, avg, min or max takes many input rows and returns one value. The GROUP BY clause decides which rows count as one input. It sorts rows into groups by the values of the grouping expressions, and then runs each aggregate once per group. A grouping expression can be a plain column such as region, or a computed expression such as a + b.
The output has one row per group, and that rule controls what you may put in the SELECT list. Every expression there must either be an aggregate or be something the rows are grouped by. Take SELECT region, customer_id, sum(amount) ... GROUP BY region. A single region holds many customer IDs, so the engine has no one value to show for customer_id, and the query fails with MISSING_AGGREGATION. The fix is one of two: group by customer_id as well, or wrap it in an aggregate.
GROUP BY ALL makes the rule automatic. It adds every SELECT-list expression that contains no aggregate to the grouping, so SELECT region, product, sum(amount) FROM sales GROUP BY ALL groups by region and product. If every expression in the SELECT list is an aggregate, GROUP BY ALL adds nothing, and the query produces one global aggregate over the whole table. The opposite mistake also fails: you cannot group by an aggregate, and doing so raises GROUP_BY_AGGREGATE.
Grouping by a column changes what a count means. SELECT customer_id, count(customer_id) FROM orders GROUP BY customer_id does not return the number of unique customers. It returns one row per customer, and each row holds how many orders that customer placed. The number of unique customers is the number of rows in that result, and a distinct count, covered in the next sections, gives it to you as a single value.
No. city is neither aggregated nor a grouping expression, and each country can contain several cities, so Databricks raises MISSING_AGGREGATION. Add city to the GROUP BY clause (or write GROUP BY ALL) to get one average per country-and-city pair.
Checkpoint 1 of 7· Check yourself
An analyst runs SELECT sum(amount), avg(amount) FROM sales GROUP BY ALL. What does Databricks SQL return?
GROUP BY ALL adds only the SELECT-list expressions that contain no aggregate. Every expression here is an aggregate, so nothing is added, and the query becomes one global aggregation over the table.
“If no such expression exist GROUP BY ALL is equivalent to omitting the GROUP BY clause which results in a global aggregation.”Source: docs.databricks.com
2.count(*), count(expr), DISTINCT and FILTER
count(*) counts rows, and count(expr) counts values that are not NULL. The two give different answers whenever the column has NULLs. Say a customers table has 230 rows and email is NULL in 30 of them. count(*) returns 230, because the asterisk counts every row in the group whether its columns are filled or not. count(email) returns 200, because it skips any row where email is NULL. count(1) behaves like count(*): the constant 1 is never NULL, so every row counts. Putting count(*) and count(email) in the same SELECT list is valid, and comparing the two is a quick way to measure how many rows are missing a value.
> SELECT count(*) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
4
> SELECT count(1) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
4
> SELECT count(col) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
3
> SELECT count(col) FILTER(WHERE col < 10)
FROM VALUES (NULL), (5), (5), (20) AS tab(col);
2With several arguments, count only counts a row when none of the arguments is NULL. count(col1, col2) skips a row if either column is NULL, so a row like (5, NULL) is not counted. People often expect it to count rows where at least one column has a value, and it does not.
count(DISTINCT expr) removes duplicate values first and then counts what is left, and the result is exact. To find how many unique customers placed at least one order, use SELECT count(DISTINCT customer_id) FROM orders. It counts each customer once, however many orders they placed. The DISTINCT keyword works the same way in other aggregates: duplicates are removed before the function sees the values. So sum(DISTINCT customer_id) adds up the ID numbers themselves, which is a meaningless total and not a count of customers.
FILTER limits one aggregate to the rows that match its condition, and the other aggregates in the query are unaffected. A WHERE clause removes rows from the whole query (see filtering rows with WHERE). A FILTER clause applies only to the aggregate it is attached to. That lets one pass over the table return several conditional counts, for example count(*) next to count(*) FILTER (WHERE status = 'returned').
Checkpoint 2 of 7· Fill the gap
The query below runs over four rows, and one of them is NULL. It returns 3. Which argument completes the count call?
> SELECT count( ? ) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
3count(col) skips the row where col is NULL, so 3 of the 4 rows are counted. count(*) and count(1) count every row and return 4. count(DISTINCT col) also merges the two 5s and returns 2.
Source: docs.databricks.comCheckpoint 3 of 7· 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, whileCOUNT(column)only counts rows where that specific expression is non-NULL, soCOUNT(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 mirrorCOUNT(*), 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, andCOUNT(email)is the one that drops rows whereemailitself is NULL. - D. This is incorrect because Databricks SQL fully supports listing
COUNT(*)alongsideCOUNT(column)in the same SELECT clause; there is no restriction that forces only one of them to execute.
3.approx_count_distinct: trading exactness for speed
An exact distinct count over a very large table is expensive, and often nobody needs the exact figure. Take a dashboard tile that shows unique devices per day from a clickstream table with billions of rows. A figure that is correct to within a few percent is just as useful there, and it returns sooner. Approximate aggregates are designed for this case. They speed up processing and cut cost whenever you don't need a precise result.
approx_count_distinct(expr) estimates how many distinct values a group contains. It does not deduplicate every value. It uses the dense version of HyperLogLog++ (HLL++), a cardinality estimation algorithm, and returns a BIGINT. Like count(DISTINCT), it works per group under GROUP BY, accepts a FILTER clause, and can run as a window function with OVER.
> SELECT approx_count_distinct(col1) FILTER(WHERE col2 = 10)
FROM VALUES (1, 10), (1, 10), (2, 10), (2, 10), (3, 10), (1, 12) AS tab(col1, col2);
3The optional second argument, relativeSD, sets how large an error is acceptable. It is the maximum relative standard deviation allowed in the estimate. By default, results fall within 5% of the true value. Passing a smaller relativeSD, as in approx_count_distinct(device_id, 0.01), asks for a tighter estimate. That 5% describes the error in the result. It does not describe how much of the table is read: the function still processes every row in the group. Reading only part of the table is a separate tool. TABLESAMPLE draws a random sample of a set percentage of rows, and you can then compute approximate aggregates over that sample. LIMIT is different again: it just takes the first rows it reaches, so the result is neither random nor spread across the dataset.
| Approach | What it reads | Result | Use it when |
|---|---|---|---|
| COUNT(DISTINCT expr) | Every row in the group | Exact count, NULL excluded | A compliance or finance figure must be exact |
| approx_count_distinct(expr[, relativeSD]) | Every row in the group, estimated with HLL++ | Estimate, within 5% by default | Latency and cost matter more than the exact digit, as on a large dashboard tile |
| TABLESAMPLE plus an aggregate | A random sample of rows at a chosen percent | Approximation based on the sample | You want to explore a huge table quickly |
Spark SQL includes two more approximate functions for other questions: approx_percentile for percentiles and approx_top_k for the most frequent values. Both make the same trade of exactness for speed. Use the exact form whenever the number will be audited or reconciled against another system. Use the approximate form when the same query runs many times on a large table and an answer within the error bound is acceptable.
Checkpoint 4 of 7· Check yourself
A teammate says approx_count_distinct(device_id) is faster than count(DISTINCT device_id) because it scans only 5% of the clickstream rows. How should you correct them?
The 5% describes how far the estimate may be from the true value, and relativeSD configures it. approx_count_distinct still reads every row. Reading only a random share of rows is a separate tool, TABLESAMPLE, and LIMIT takes the first rows it reaches without any randomness.
“Results are accurate within a default value of 5%, which derives from the value of the maximum relative standard deviation”Source: docs.databricks.com
Checkpoint 5 of 7· Exam question
A dashboard aggregates a multi-billion-row clickstream table to show the approximate number of unique device_id values per day, and query latency for the dashboard tile is a higher priority than exact precision. Why would approx_count_distinct(device_id) be preferred over COUNT(DISTINCT device_id) for this tile?
Correct answer: A — It uses the HyperLogLog++ algorithm to estimate cardinality with bounded memory, returning results faster at the cost of a small error margin
- A. This is correct because
approx_count_distinctimplements the dense HyperLogLog++ algorithm, which estimates cardinality using a fixed, small amount of memory instead of tracking every distinct value, trading a default ~5% relative error for much lower latency on huge tables. - B. This is incorrect because the function does not reproduce the exact
COUNT(DISTINCT)result; it is an approximation by design, and the difference is in the estimation algorithm, not in output formatting. - C. This is incorrect because
approx_count_distinctis a stateless aggregate function evaluated per query execution; it does not create or maintain a persistent cache of prior results across dashboard refreshes. - D. This is incorrect because the function scans all rows that reach the aggregate; its speed comes from the compact HLL++ sketch structure, not from sampling or skipping a portion of the underlying data.
Checkpoint 6 of 7· 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_distincttrades 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_idwithout 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.
4.Mean, median, standard deviation and summary statistics
avg and mean are the same function, and both return the arithmetic mean of a group's values. They skip NULLs, just as count(expr) does, so the average of 1, 2 and NULL is 1.5 and not 1. If a group is empty or contains only NULLs, the result is NULL, not zero. Adding DISTINCT removes duplicates before averaging. Decide on purpose whether NULL should mean "leave this row out" or "treat it as zero". If it should be zero, replace the NULLs before you aggregate, as described in handling missing values.
> SELECT avg(DISTINCT col) FROM VALUES (1), (1), (2) AS tab(col);
1.5
> SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
1.5The result type of avg depends on its input. A DECIMAL(p, s) column averages to DECIMAL(p + 4, s + 4). Year-month and day-time intervals average to intervals. All other numeric inputs return a DOUBLE. If the result is too large for its type, avg fails with ARITHMETIC_OVERFLOW. try_avg returns NULL in that case instead, which is the safer choice for a dashboard query that should not break.
median returns the middle value of a group, and it is defined as percentile_cont(0.5) WITHIN GROUP (ORDER BY expr). It depends on where values sit in sorted order, not on how large they are, so one extreme value barely moves it. Over 1, 2, 2, 3, 4 the median is 2. If you replace the 4 with 4,000, the middle value is still 2, while the mean rises from 2.4 to about 801. That makes the median the better choice for skewed measures such as order value or session length. Like avg, it ignores NULLs, and DISTINCT changes the answer, as the example shows.
> SELECT median(col) FROM VALUES (1), (2), (2), (3), (4), (NULL) AS tab(col);
2
> SELECT median(DISTINCT col) FROM VALUES (1), (2), (2), (3), (4), (NULL) AS tab(col);
2.5stddev, also written std or stddev_samp, measures spread as the sample standard deviation and returns a DOUBLE. A sample statistic needs at least two values, so a group with only one row returns NULL, not 0. This shows up after a fine-grained GROUP BY: a product with a single sale gets a NULL spread.
| Statistic | Function | NULL inputs | Edge case |
|---|---|---|---|
| Row count | count(*) | Counted | Empty table gives 0 rows counted |
| Non-NULL count | count(expr) | Skipped | Multi-argument form needs every argument non-NULL |
| Mean | avg / mean | Skipped | Empty or all-NULL group gives NULL; overflow raises ARITHMETIC_OVERFLOW |
| Median | median | Skipped | Same as percentile_cont(0.5) |
| Spread | stddev / std | Skipped | One-row group gives NULL |
| Range | min, max | Skipped | Combine with the others in one SELECT |
In SQL, a full statistical profile is a single SELECT that lists several aggregates: count(price), avg(price), stddev(price), min(price), median(price), max(price), plus a GROUP BY if you want one profile per segment. In a notebook, the PySpark DataFrame methods describe() and summary() produce the same profile in one call. describe() reports count, mean, stddev, min and max. summary() adds approximate percentiles such as 25%, 50% and 75%, and lets you choose which statistics to compute. Both are intended for exploratory analysis, and their output schema is not guaranteed to stay the same across versions.
Checkpoint 7 of 7· Fill the gap
This avg example from the Databricks reference runs over three rows, and one of them is NULL. Which value completes the result line?
> SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
? avg skips NULLs, so it averages only 1 and 2 and returns 1.5. It does not treat the NULL as zero, which would give 1.0, and one NULL input does not make the whole result NULL. The result is NULL only when every value in the group is NULL or the group is empty.
Source: docs.databricks.comSources
(10)
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
“group the rows based on a set of specified grouping expressions and compute aggregations on the group of rows”
↩︎ How GROUP BY collapses rows into groups“A shorthand notation to add all SELECT-list expressions not containing aggregate functions as group_expressions.”
↩︎ How GROUP BY collapses rows into groups“If no such expression exist GROUP BY ALL is equivalent to omitting the GROUP BY clause which results in a global aggregation.”
↩︎ How GROUP BY collapses rows into groups“Databricks raises UNRESOLVED_ALL_IN_GROUP_BY or MISSING_AGGREGATION if the produced clause is not well-formed.”
↩︎ How GROUP BY collapses rows into groups“If group_expression contains an aggregate function Databricks raises a GROUP_BY_AGGREGATE error.”
↩︎ How GROUP BY collapses rows into groups“DISTINCT Removes duplicates in input rows before they are passed to aggregate functions.”
↩︎ count(*), count(expr), DISTINCT and FILTER“When a FILTER clause is attached to an aggregate function, only the matching rows are passed to that function.”
↩︎ count(*), count(expr), DISTINCT and FILTER“compute aggregations on the group of rows based on one or more specified aggregate functions”
↩︎ Key concept - 2.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-havingOfficial docs
“If the expression references columns that are neither in the GROUP BY clause nor wrapped in aggregate functions, Databricks raises MISSING_AGGREGATION.”
↩︎ How GROUP BY collapses rows into groups - 3.
“expr: Counts all rows for which all exprN are not NULL.”
↩︎ count(*), count(expr), DISTINCT and FILTER“*: Counts all rows in the group.”
↩︎ count(*), count(expr), DISTINCT and FILTER“*: Counts all rows in the group.”
↩︎ Exam trap 1“If DISTINCT is specified then the function returns the number of unique values which do not contain NULL.”
↩︎ Exam trap 2“If DISTINCT is specified then the function returns the number of unique values which do not contain NULL.”
↩︎ Prediction - 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“You can also specify a sample percent with TABLESAMPLE to generate a random sample from a dataset and calculate approximate aggregates.”
↩︎ approx_count_distinct: trading exactness for speed“does not introduce randomness, or guarantee that sampling is distributed across the dataset.”
↩︎ approx_count_distinct: trading exactness for speed - 5.
“The implementation uses the dense version of the HyperLogLog++ (HLL++) algorithm, a state of the art cardinality estimation algorithm.”
↩︎ approx_count_distinct: trading exactness for speed“relativeSD: Defines the maximum relative standard deviation allowed.”
↩︎ approx_count_distinct: trading exactness for speed“Results are accurate within a default value of 5%, which derives from the value of the maximum relative standard deviation”
↩︎ Exam trap 3“Results are accurate within a default value of 5%, which derives from the value of the maximum relative standard deviation”
↩︎ Checkpoint - 6.
“This function is a synonym for mean aggregate function.”
↩︎ Mean, median, standard deviation and summary statistics“If the result overflows the result type, Databricks raises an ARITHMETIC_OVERFLOW error. To return a NULL instead use try_avg.”
↩︎ Mean, median, standard deviation and summary statistics“Nulls within the group are ignored. If a group is empty or consists only of nulls, the result is NULL.”
↩︎ Exam trap 5 - 7.
“This function is a synonym for percentile_cont(0.5) WITHIN GROUP (ORDER BY expr).”
↩︎ Mean, median, standard deviation and summary statistics - 8.
“Returns the sample standard deviation calculated from the values in the group. This function is a synonym for std aggregate function.”
↩︎ Mean, median, standard deviation and summary statistics“If any group consists of only one row, the function returns NULL for that group.”
↩︎ Exam trap 4 - 9.
“Available statistics are: count, mean, stddev, min, max, arbitrary approximate percentiles specified as a percentage (e.g., 75%).”
↩︎ Mean, median, standard deviation and summary statistics - 10.
“Use summary for expanded statistics and control over which statistics to compute.”
↩︎ Mean, median, standard deviation and summary statistics