Aggregating: Advanced

Collect grouped values into a single array

ARRAY_AGG collects all values in a group into an array. Unlike SUM or COUNT which produce a single number, ARRAY_AGG preserves every individual value in a structured format that's easy for downstream systems to consume.

ARRAY_AGG collects multiple values into a single array column. This preserves detail while still grouping data.

ARRAY_AGG takes all values from a column within each group and returns them as a single array.

Analysts sometimes prefer readable strings over arrays. ARRAY_JOIN converts an array to a delimited string for display:

Practice using ARRAY_AGG to collect values into a list grouped by a category.

SELECT
  department,
  ___(___) AS all_metrics
FROM employee_metrics
GROUP BY department

ARRAY_AGG is a natural fit for building team rosters, product tag lists, and activity trails. Anywhere you currently use comma-joined strings, ARRAY_AGG is the structured upgrade.

Arrays produced by ARRAY_AGG can be passed directly to downstream systems like Python, Spark, or JSON APIs without any string parsing, which eliminates a whole class of data pipeline bugs.

Build key-value lookups from grouped data

MAP_AGG builds a key-value map (dictionary) from two columns. While ARRAY_AGG collects values into a list, MAP_AGG creates structured lookups where each key maps to a value.

MAP_AGG creates key-value dictionaries from two columns, enabling direct lookups without parsing.

MAP_AGG takes two columns and builds key-value pairs for each group.

For each customer, this creates a map where product names are keys and quantities are values. The result is a MAP type that provides O(1) key lookup.

In large-scale pipelines, MAP_AGG often eliminates the need for expensive joins back to configuration tables.

Once you have a map, access values using bracket notation or the ELEMENT_AT function:

Missing keys return NULL. This is useful for sparse data where not all entities have all attributes.

SELECT
  source,
  ___(___, ___) AS feature_types
FROM ml_features
GROUP BY source

MAP_AGG pivots multiple rows of key-value pairs into a single map per group, eliminating the need for self-joins on configuration tables.

After building a map with MAP_AGG, use bracket notation like settings['color'] to access individual values efficiently.

Control the order inside aggregated results

Both ARRAY_AGG and other aggregate functions support modifiers that control their behavior: ORDER BY for sequencing, and DISTINCT for removing duplicates.

By default, the order of aggregated values is not guaranteed. Use ORDER BY inside the aggregate to control the sequence:

Now employees appear in order of hire date (earliest first). You can use any column and ASC or DESC for ordering.

ORDER BY is especially useful for building ordered sequences like activity trails or event timelines:

DISTINCT inside aggregates removes duplicate values before collection, giving you unique entries only.

Add DISTINCT to remove duplicate values before aggregating:

Even if a customer ordered multiple Electronics products, the category appears only once. Without DISTINCT, it would repeat.

DISTINCT inside ARRAY_AGG requires hashing or sorting, so it adds computational overhead. Only use it when uniqueness matters for your downstream consumers.

Count only rows that match a condition

Sometimes you need to count only rows that meet a certain condition. For example: "How many orders are completed vs pending?" You could write two separate queries, but COUNT_IF lets you do it in one.

COUNT_IF provides a concise way to count rows matching specific conditions without complex CASE expressions.

COUNT_IF counts only rows where the condition is TRUE:

You can combine conditions using AND and OR within your conditional count:

COUNT_IF enables counting multiple conditions in a single query, unlike WHERE which filters before aggregation.

Watch how COUNT_IF iterates through each row, evaluates the condition, and only counts matching rows:

SELECT
  category,
  COUNT(*) AS total_orders,
  ___(status = ___) AS completed,
  ___(status = ___) AS pending
FROM orders
___ category

COUNT_IF replaces a pattern that would otherwise require multiple subqueries or a CASE WHEN inside SUM. It expresses conditional counting in the most direct, readable way possible.

Once you start using COUNT_IF, you will find yourself replacing WHERE-filtered subqueries throughout your codebase. A single pass through the data is always faster than multiple filtered scans.

Split one aggregation into multiple buckets

COUNT_IF works great for counting, but what about SUM, AVG, and other aggregates? Use CASE WHEN to conditionally include values in any aggregate function.

CASE WHEN inside aggregates lets you apply different logic to different rows. The pattern differs for SUM versus AVG.

CASE WHEN inside SUM or AVG lets you selectively include values based on row-level conditions.

Notice the critical difference: SUM uses ELSE 0 to avoid counting non-managers, while AVG omits ELSE entirely. Including ELSE 0 in AVG would drag down the average by counting zeros.

Advanced aggregates require careful NULL handling to avoid unexpected results.

When collecting values into arrays, NULL values are typically excluded by default. Use COALESCE or filtering to control this behavior:

Which query safely handles groups where all values are NULL?

Try using the SUM and CASE WHEN pattern to conditionally count rows within each group.

SELECT
  category,
  ___(
    ___
      WHEN status = 'active' THEN 1
      ELSE 0
    END
    ) AS active_count
FROM products
GROUP BY category

The CASE WHEN pattern inside aggregates is universally supported across all major SQL engines: Trino, Spark SQL, BigQuery, Snowflake, PostgreSQL, and more. It is a transferable skill regardless of which platform you use.