Aggregating Data into Scalar Metrics

In this lesson, you will learn how to condense entire tables of data into single, meaningful values called scalar metrics.

From Many Rows to One Value

So far, our queries have mostly returned lists of individual records. However, in data analysis, we often need to step back and look at the "big picture." Instead of seeing every single row, we want to summarize them into a single number—a scalar metric.

To do this, we use aggregate functions. These functions look at a scope of data (usually the entire result set) and perform a calculation to produce one final output.

Common Scalar Metrics

Think of these as the fundamental tools for summarizing your database:

• COUNT(): Tells you how many rows exist.
• AVG(): Calculates the arithmetic mean of a numeric column.
• MIN() and MAX(): Identify the smallest or largest values in a range.

Imagine you have a table called products with a column named price. If you want to understand your inventory, you don’t need a list of every product; you need a summary.

SELECT 
    COUNT(*) AS total_products,
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products;

How It Works

When you run a query like the one above, the database engine ignores individual row identifiers. Instead, it processes the entire scope (the FROM clause) and reduces it down to exactly one row containing your calculated metrics.

Because you are reducing many values into a single scalar, you cannot include non-aggregated columns (like product_name) in the SELECT list alongside these functions. If you tried, the database wouldn't know which specific row's name to display since the results have been "collapsed" into a single summary line. By using aliasing—which you learned in a previous lesson—you can ensure your scalar results are clearly labeled, making them ready for reports or dashboard displays.

Key Takeaway: Aggregate functions allow you to transform a large set of raw data into high-level scalar metrics like totals, averages, and extremes, providing a concise summary of your data.