Aggregate Data for High-Level Insights

Learning Objectives

After completing this unit, you’ll be able to:

• Calculate aggregate values using functions like COUNT, SUM, and AVG.
  
• Group query results using the GROUP BY clause.
  
• Filter grouped data using the HAVING clause.
  

Summarize Data for Business Intelligence

After mastering the basics of selecting and filtering data, the next critical step is to turn raw data into high-level metrics and business insights. Raw data, even when filtered, can be overwhelming. Data 360 SQL’s aggregate functions allow you to compute a single result from a set of values, such as the total purchases, average age, or count of customers.

These techniques are essential for creating reports to understand macro trends and business insights for stakeholders.

Use Aggregate Functions

Aggregate functions perform calculations on a set of rows and return a single, summary value. You typically place these functions in the SELECT clause.

Here are the most common functions.

For a quick, high-level summary of all customer transactions, use aggregate functions to calculate the total number of orders, the total revenue, and the average order value (AOV) from the ssot__SalesOrder_dlm DMO.

SELECT COUNT(*) AS "Total_Orders",
       SUM("OrderTotalAmount__c") AS "Total_Revenue",
       AVG("OrderTotalAmount__c") AS "Average_Order_Value"
FROM "ssot__SalesOrder__dlm"

Each function processes the entire set of records in the DMO and returns a single row containing these three calculated metrics.

Group Results with GROUP BY

A single aggregate value, like total revenue, is useful, but analysis usually requires these metrics broken down by categories (or groups). For example, you might need the total revenue broken down by Sales Channel or the customer count broken down by Country. To see the average order value for each country, follow this example.

To perform calculations for each unique group, you use the GROUP BY clause.

• Include the category field you want to group by in your SELECT list.
  
• Include that same category field in the GROUP BY clause.
  

For example, if you need to see the average order value for each country, your query would look like:

SELECT "ssot__CountryId__c" AS "Country",
       AVG("OrderTotalAmount__c") AS "Average_Order_Value"
FROM "ssot__SalesOrder_dlm"
GROUP BY "ssot__CountryId__c"

The query calculates the average order value (AOV) separately for every unique value found in the ssot__CountryId__c field.

Any nonaggregated field in the SELECT clause must also be included in the GROUP BY clause.

Filter Groups with HAVING

You already know the WHERE clause filters individual rows before they are grouped and aggregated. To apply conditions to the summary results—the groups themselves—you use the HAVING clause.

The HAVING clause filters the output of the aggregate functions. It always comes after the GROUP BY clause.

If you are only interested in countries that have a high sales volume (meaning the average order value is greater than $500), this query first groups the orders by country, calculates the AOV for each, and then filters the final list to include only those countries that meet the $500 threshold.

SELECT "ssot__CountryId__c" AS "Country",
       AVG("OrderTotalAmount__c") AS "Average_Order_Value"
FROM "ssot__SalesOrder_dlm"
GROUP BY "ssot__CountryId__c"
HAVING AVG("OrderTotalAmount__c") > 500

Now that you can start turning data into metrics and business insights, the next unit helps you dive into combining data from multiple sources. Now it’s time to check your skills in this hands-on challenge!