What you will be able to do
- Use avg and mean interchangeably and predict their result type and their behaviour with NULLs, empty groups and DISTINCT
- Handle overflow in an average with try_avg instead of avg
- Compute summary statistics with median and stddev, and explain when each one returns NULL
1.avg and mean: one function under two names
In Databricks SQL, avg and mean are the same function. The avg page says it 'is a synonym for mean aggregate function', and the mean page says the reverse. Both have the form avg([ALL | DISTINCT] expr) [FILTER (WHERE cond)], and both can be used as window functions with OVER. If an exam option uses mean where you expected avg, it is not a trick.
SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);The answer is 1.5. NULLs are not counted as zero. They are left out of both the sum and the row count: 'Nulls within the group are ignored. If a group is empty or consists only of nulls, the result is NULL.' That second sentence also matters. A group with no non-NULL values returns NULL, not 0. DISTINCT works as it did with count: 'the average is computed after duplicates have been removed.' So avg(DISTINCT col) over 1, 1, 2 is 1.5, while a plain avg over the same values would give a different answer.
The type of the result depends on the type of the input. For DECIMAL inputs, Databricks adds four digits of precision and four of scale. If the maximum DECIMAL precision is reached, it limits the increase in scale so that no significant digits are lost. Averaging intervals returns an interval: two year-month intervals of 1 and 2 years average to 1-6, or one year and six months. Everything else becomes a DOUBLE, which is why avg over the integers 1, 2, 3 returns 2.0 and not 2.
| Input type | Result type |
|---|---|
| DECIMAL(p, s) | DECIMAL(p + 4, s + 4) |
| year-month interval | INTERVAL YEAR TO MONTH |
| day-time interval | INTERVAL DAY TO SECOND |
| Any other numeric type | DOUBLE |
A wider result type still has limits. If the average overflows its result type, avg raises ARITHMETIC_OVERFLOW. The documented example averages two DECIMAL(38, 0) values of 5e37. try_avg is the safe version: on overflow it returns NULL instead of failing the query. In Databricks Runtime with spark.sql.ansi.enabled set to false, avg itself also returns NULL on overflow. The documented example shows the error happening in ANSI mode.
Checkpoint 1 of 5· Fill the gap
This average of two very large DECIMAL values would overflow. Which function makes the query return NULL instead of raising ARITHMETIC_OVERFLOW?
SELECT ? (col) FROM VALUES (5e37::DECIMAL(38, 0)), (5e37::DECIMAL(38, 0)) AS tab(col);try_avg returns NULL when the result overflows. avg and its synonym mean raise ARITHMETIC_OVERFLOW in ANSI mode.
Source: docs.databricks.comCheckpoint 2 of 5· Check yourself
A WHERE clause leaves a region with rows whose discount column is always NULL. What does avg(discount) return for that region?
NULLs are ignored, so the region has no values to average. A group that consists only of NULLs returns NULL, not zero.
“Nulls within the group are ignored. If a group is empty or consists only of nulls, the result is NULL.”Source: docs.databricks.com
Checkpoint 3 of 5· 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_distinct` implements 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_distinct` is 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.
2.median: the middle value, which outliers don't pull
A mean is affected by extreme values. A summary that holds up better against outliers is the median, available on Databricks SQL and Databricks Runtime 11.3 LTS and above. It is not a separate algorithm: the reference says it 'is a synonym for percentile_cont(0.5) WITHIN GROUP (ORDER BY expr).' Its NULL handling is the same as avg: NULLs are ignored, and an empty or all-NULL group returns NULL. It accepts numeric or interval input, returns an interval for interval input, and returns a DOUBLE otherwise.
SELECT median(DISTINCT col) FROM VALUES (1), (2), (2), (3), (4), (NULL) AS tab(col);Without DISTINCT, the five non-NULL values are 1, 2, 2, 3, 4, and the middle one is 2. With DISTINCT the duplicate 2 is removed, leaving 1, 2, 3, 4. With an even number of values, percentile_cont(0.5) falls between 2 and 3, which gives 2.5.
Checkpoint 4 of 5· Check yourself
Which expression is documented as equivalent to median(price)?
median is defined as a synonym for the continuous 50th percentile of the ordered values.
“This function is a synonym for percentile_cont(0.5) WITHIN GROUP (ORDER BY expr).”Source: docs.databricks.com
Sources3
3.stddev: how spread out the values are
Count, mean and median describe the size and centre of a group. Standard deviation describes how spread out it is. stddev 'returns the sample standard deviation calculated from the values in the group' and is a synonym for std. It takes numeric input, accepts DISTINCT and FILTER, can be used as a window function, and always returns a DOUBLE. For the values 1, 2, 3, 3 the documented result is 0.9574271077563381. With DISTINCT it runs on 1, 2, 3 only and returns 1.0.
SELECT stddev(col) FROM VALUES (1), (2), (3), (3) AS tab(col);Because it is a sample statistic, a group with only one row has no spread to measure: 'If any group consists of only one row, the function returns NULL for that group.' In a GROUP BY report, small categories can therefore show a NULL standard deviation next to a perfectly valid count of 1 and a valid mean. That NULL is expected behaviour, not missing data. Putting count, avg, median and stddev in one SELECT gives you a summary-statistics row per group. Databricks applies the same idea in data profiling, which 'uses aggregate statistics and data distributions to track data quality over time.'
Checkpoint 5 of 5· Check yourself
A report groups sales by store and computes count(*), avg(amount) and stddev(amount). A new store has exactly one sale. What does its row show?
count and avg are both defined for a single row. stddev computes a sample standard deviation and returns NULL for a group with only one row. It does not return zero or raise an error.
“If any group consists of only one row, the function returns NULL for that group.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.avg treats NULL as zero, and returns 0 when there are no values.Why is that wrong?
NULLs are left out of the average entirely, so the values 1, 2, NULL average to 1.5. A group that is empty or all NULL returns NULL.
Covered in avg and mean: one function under two names
2.stddev of a single-row group is 0.Why is that wrong?
stddev is a sample standard deviation and returns NULL for any group that has only one row.
Covered in stddev: how spread out the values are
Practise it for real
Check the NULL and DISTINCT behaviour of avg, median and stddev yourself by running the documented VALUES queries in the SQL editor.
1.Run SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
Why: Shows that NULLs are left out of the average rather than counted as zero.
You should see: 1.5
2.Run SELECT avg(DISTINCT col) FROM VALUES (1), (1), (2) AS tab(col);
Why: Shows that DISTINCT removes duplicates before averaging.
You should see: 1.5
3.Run SELECT median(col) FROM VALUES (1), (2), (2), (3), (4), (NULL) AS tab(col); then repeat it with median(DISTINCT col).
Why: Shows how removing a duplicate changes which value is in the middle.
You should see: 2 without DISTINCT, 2.5 with DISTINCT
4.Run SELECT stddev(col) FROM VALUES (1), (2), (3), (3) AS tab(col); then repeat it with stddev(DISTINCT col).
Why: Shows the sample standard deviation with and without duplicates.
You should see: 0.9574271077563381, then 1.0
Stuck? Get a nudge
Before each run, write down your predicted result, then compare it with the output.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“This function is a synonym for mean aggregate function.”
↩︎ avg and mean: one function under two names“If the result overflows the result type, Databricks raises an ARITHMETIC_OVERFLOW error. To return a NULL instead use try_avg.”
↩︎ avg and mean: one function under two names“If DISTINCT is specified the average is computed after duplicates have been removed.”
↩︎ avg and mean: one function under two names“Nulls within the group are ignored. If a group is empty or consists only of nulls, the result is NULL.”
↩︎ Exam trap 1“Nulls within the group are ignored. If a group is empty or consists only of nulls, the result is NULL.”
↩︎ Checkpoint - 2.
“This function is a synonym for avg aggregate function.”
↩︎ avg and mean: one function under two names - 3.
“If DISTINCT is specified, duplicates are removed and the median is computed.”
↩︎ median: the middle value, which outliers don't pull“This function is a synonym for percentile_cont(0.5) WITHIN GROUP (ORDER BY expr).”
↩︎ Checkpoint - 4.
“Returns the sample standard deviation calculated from the values in the group.”
↩︎ stddev: how spread out the values are“If any group consists of only one row, the function returns NULL for that group.”
↩︎ Exam trap 2“If any group consists of only one row, the function returns NULL for that group.”
↩︎ Checkpoint - 5.
“Data profiling uses aggregate statistics and data distributions to track data quality over time.”
↩︎ stddev: how spread out the values are