CertSafari
    Databricks Certified Data Analyst Associate· Lessons

    Domain 4 · Lesson 13/39

    Aggregate functions in Databricks SQL: counts, distinct counts and summary statistics

    Perform aggregate operations such as count, approximate count distinct, mean, and summary statistics.

    16 min read
    2.22% of exam
    10 sources
    Published 22 Oct 2026
    Docs as of 30 Sep 2026

    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.

    Checkpoint 1 of 7· Check yourself

    An analyst runs SELECT sum(amount), avg(amount) FROM sales GROUP BY ALL. What does Databricks SQL return?

    Sources12

    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.

    The same four rows, one of them NULL, counted four wayssql
    > 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);
     2

    With 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);
     3

    Checkpoint 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?

    Sources31

    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.

    Signature, and a filtered estimate over a small set of valuessql
    > 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);
     3

    The 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.

    Three ways to count distinct values, and when each one is the right choice
    ApproachWhat it readsResultUse it when
    COUNT(DISTINCT expr)Every row in the groupExact count, NULL excludedA compliance or finance figure must be exact
    approx_count_distinct(expr[, relativeSD])Every row in the group, estimated with HLL++Estimate, within 5% by defaultLatency and cost matter more than the exact digit, as on a large dashboard tile
    TABLESAMPLE plus an aggregateA random sample of rows at a chosen percentApproximation based on the sampleYou 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?

    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?

    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?

    Sources45

    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.

    DISTINCT removes duplicates before averaging, and NULL is ignoredsql
    > 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.5

    The 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.

    Removing the duplicate 2 changes which value sits in the middlesql
    > 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.5

    stddev, 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.

    Summary statistics in Databricks SQL and how each handles edge cases
    StatisticFunctionNULL inputsEdge case
    Row countcount(*)CountedEmpty table gives 0 rows counted
    Non-NULL countcount(expr)SkippedMulti-argument form needs every argument non-NULL
    Meanavg / meanSkippedEmpty or all-NULL group gives NULL; overflow raises ARITHMETIC_OVERFLOW
    MedianmedianSkippedSame as percentile_cont(0.5)
    Spreadstddev / stdSkippedOne-row group gives NULL
    Rangemin, maxSkippedCombine 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);
      ? 

    Sources678910

    Sources

    (10)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “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. 2.
      “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. 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. 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. 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. 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. 7.
      “This function is a synonym for percentile_cont(0.5) WITHIN GROUP (ORDER BY expr).”
      ↩︎ Mean, median, standard deviation and summary statistics
    8. 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. 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. 10.
      “Use summary for expanded statistics and control over which statistics to compute.”
      ↩︎ Mean, median, standard deviation and summary statistics

    Spotted a mistake, or was something unclear? Tell us.