DP-900: how to use aggregation functions in analytical queries

I will teach you the DP-900 competency about aggregation functions in analytical queries: what they are, how to use them in Azure services (for example Azure SQL Database, Azure Synapse, and Power BI) and why this is essential both for the exam and for building real reports and analyses.

What you need to know

Aggregation functions summarize sets of rows into single values — for example, counting records, summing values or calculating averages. They are fundamental for reports, dashboards and analytical queries. Basic aggregations include: COUNT, SUM, AVG, MIN and MAX. To group rows by categories use GROUP BY. In Azure contexts, these queries run on SQL engines like Azure SQL Database and Synapse SQL or are translated into operations in Power BI/DirectQuery.

Simple example: imagine a table Sales(sale_id, product_id, amount, sale_date). To know the total sales per product we use an aggregation with GROUP BY.

How it works

Key steps and rules to know:

1. SELECT with aggregation functions combines aggregated and non-aggregated columns; all non-aggregated columns must appear in GROUP BY.
2. GROUP BY creates groups of rows with equal values in the listed columns; the aggregation function computes a value for each group.
3. HAVING filters groups after aggregation (equivalent to WHERE but for aggregated results).
4. ORDER BY arranges the results; commonly useful for top N (e.g. top 10 products by sales).

-- Total de vendas por produto  SELECT product_id,         SUM(amount) AS total_sales,         COUNT(*) AS sales_count,         AVG(amount) AS avg_sale  FROM Sales  GROUP BY product_id  ORDER BY total_sales DESC;  
-- Filtrar produtos com mais de 100 vendas  SELECT product_id,         SUM(amount) AS total_sales,         COUNT(*) AS sales_count  FROM Sales  GROUP BY product_id  HAVING COUNT(*) > 100;

Note: on different engines the syntax is very similar. In Power BI, when writing DAX measures you perform aggregations with functions like SUM and AVERAGE, but the logic of grouping and aggregating also applies.

In practice

Practical workflow to create an analytical query with aggregations in an Azure environment:

1. Identify the table and relevant columns (e.g.: Sales.amount, Sales.product_id).
2. Define the desired level of granularity (by product, by month, by region).
3. Choose the appropriate aggregation functions (SUM for amounts, COUNT for counts, AVG for averages, MIN/MAX for extremes).
4. Write the query with GROUP BY on the granularity columns and with the aggregators in the SELECT.
5. Apply HAVING to restrict groups and ORDER BY to sort the output; limit with TOP or FETCH for performance if needed.

-- Vendas mensais por produto (agregação por mês)  SELECT product_id,         FORMAT(sale_date, 'yyyy-MM') AS sale_month,         SUM(amount) AS monthly_sales  FROM Sales  GROUP BY product_id, FORMAT(sale_date, 'yyyy-MM')  ORDER BY product_id, sale_month;

Performance considerations in Azure: appropriate indexes (e.g. indexes on product_id or on columns used in GROUP BY) and table partitioning in Synapse/Azure SQL can speed up aggregations on large volumes. In Synapse, consider using distributed tables to parallelize the operation.

Common mistakes

• Forgetting to include non-aggregated columns in GROUP BY — causes an error or unexpected results.
• Using HAVING instead of WHERE to filter rows before aggregation — WHERE filters individual rows before GROUP BY; HAVING filters groups after aggregation.
• Not considering performance: aggregations on large tables without indexes/partitioning can be slow; in Synapse distribute data correctly to avoid excessive shuffling (data shuffling).

How to practice

Practice by writing queries in Azure SQL Database or in a local SQL Server instance using sample datasets (AdventureWorks or sales databases). For official exercises and assessments, use the OFFICIAL and free Practice Assessment from Microsoft for DP-900 and the official Microsoft study guide (both free). Do not use dumps or fabricated questions — rely on these official resources to evaluate knowledge.

In summary

• Aggregation functions (COUNT, SUM, AVG, MIN, MAX) summarize rows to support analytical reporting.
• GROUP BY defines granularity; all non-aggregated columns in the SELECT must be in GROUP BY.
• WHERE filters before grouping; HAVING filters groups after aggregation.
• Performance: indexes, partitioning and distribution architecture in Azure (Synapse) greatly affect aggregation speed.