🇯🇵 日本語 | 🇺🇸 English | 🇪🇸 Español | 🇵🇹 Português | 🇹🇭 ไทย | 🇨🇳 中文

【SQL Functions Tutorial】Mastering Data Aggregation with SUM, AVG, MAX, and MIN!

When creating sales reports for a website, information like "What are this month's total sales?", "What's the average price of our products?", and "What was the highest-priced item sold?" is essential. When you want to take individual pieces of data stored in a database and aggregate them into a single, meaningful number, SQL's aggregate functions unleash their immense power.

An aggregate function is a function that takes data spanning multiple rows as input and returns a single value as the result. You can think of it like feeding a stack of papers with numbers written on them into a calculator to get a single answer, such as a "total" or an "average".

This article will thoroughly explain how to use the "big four" aggregate functions, which are among the most frequently used and fundamental to data analysis: SUM(), AVG(), MAX(), and MIN(), complete with copy-and-paste code. By mastering these functions and combining them with GROUP BY, your data analysis capabilities will improve dramatically!

Preparation: Let's Get Our Sales Data Ready for Aggregation

To try out aggregate functions, we first need some numerical data to work with. This time, we'll create a sales table that mimics an e-commerce site's sales history. By including data from multiple categories and with different prices and quantities, the function of each aggregate function will become clearer.

-- Drop the sales table if it exists (for repeatable testing)
DROP TABLE IF EXISTS sales;

-- Create a new sales table
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  price INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  sale_date DATE NOT NULL
);

-- Insert initial data
INSERT INTO sales (id, product_name, category, price, quantity, sale_date) VALUES
(1, 'Amazing Ballpoint Pen', 'Stationery', 150, 10, '2025-07-01'),
(2, 'Magic Notebook', 'Stationery', 300, 5, '2025-07-01'),
(3, 'Ultimate Rice Cooker', 'Appliances', 25000, 2, '2025-07-02'),
(4, 'Miracle Apple', 'Food', 500, 20, '2025-07-03'),
(5, 'USB-C Hub', 'PC Peripherals', 4000, 3, '2025-07-03'),
(6, 'Traditional Brush', 'Stationery', 1200, 8, '2025-07-04'),
(7, 'Premium Chocolate', 'Food', 3500, 5, '2025-07-05'),
(8, 'Amazing Ballpoint Pen', 'Stationery', 150, 15, '2025-07-05');

Now we're all set to aggregate data from various angles.

【Total】Finding the Sum with the `SUM()` Function

The SUM() function calculates the total value of a specified numeric column. It's one of the most basic aggregate functions, used in countless business scenarios where you need to know a "total," such as "total sales," "total inventory," or "total points."

You specify the column you want to sum up inside the parentheses of SUM(). It's also possible to sum the result of a calculation between columns.

Scenario: "What is the total amount of all sales (total revenue)?"

In this case, we calculate "price * quantity" for each record and then sum up all of those results.

SELECT SUM(price * quantity) AS total_revenue FROM sales;

Result:

total_revenue
-------------
102850

Using AS total_revenue to give the result column a clear, meaningful name is also a key point.

【Average】Finding the Average Value with the `AVG()` Function

The AVG() (short for Average) function calculates the average value of a specified numeric column. It's useful for understanding overall trends in your data, such as "average age," "average score," or "average unit price."

Scenario: "What is the average unit price of the products sold?"

SELECT AVG(price) AS average_price FROM sales;

Result:

average_price
-------------
5562.5

As a point of caution, remember that if the column being calculated contains `NULL` values, `SUM()` and `AVG()` will ignore those rows in their aggregation. This can sometimes lead to unintended results.

【Maximum & Minimum】Finding the Max and Min Values with `MAX()` and `MIN()`

MAX() (Maximum) and MIN() (Minimum) are, as their names suggest, functions for retrieving the maximum and minimum values in a column. It's efficient to learn these two as a pair.

The `MAX()` Function: Finding the Largest Value

Scenario: "What was the highest-value sale in a single transaction?"

SELECT MAX(price * quantity) AS highest_sale FROM sales;

Result:

highest_sale
------------
50000

That's the sale for the 'Ultimate Rice Cooker' with ID 3 (25,000 * 2).

The `MIN()` Function: Finding the Smallest Value

Scenario: "Among the products sold, what is the cheapest unit price?"

SELECT MIN(price) AS cheapest_price FROM sales;

Result:

cheapest_price
--------------
150

MAX() and MIN() are not just for numbers; they can also be used on dates (to find the latest/earliest date) and strings (to find the last/first in alphabetical order), making them very versatile functions.

【The Ultimate Combination】The `GROUP BY` Clause and Aggregate Functions

The true power of these four aggregate functions is most evident when they are combined with the GROUP BY clause. Using GROUP BY, you can group data by the values in a specific column and then apply aggregate functions to **each** of those groups.

This enables more practical and detailed analysis, such as "total sales per category" or "average number of units sold per product."

Scenario: "For each product category, I want to aggregate the total revenue, average unit price, and total quantity sold."

SELECT
  category,
  SUM(price * quantity) AS total_revenue,
  AVG(price) AS average_price,
  SUM(quantity) AS total_quantity_sold
FROM
  sales
GROUP BY
  category;

Result:

category       | total_revenue | average_price | total_quantity_sold
---------------|---------------|---------------|---------------------
PC Peripherals | 12000         | 4000          | 3
Appliances     | 50000         | 25000         | 2
Stationery     | 13350         | 550           | 33
Food           | 27500         | 2000          | 25

With this single query, the detailed sales performance for each category is now clear at a glance. By combining aggregate functions with `GROUP BY` in this way, you can extract business-critical "insights" from raw data that was once just a list of numbers.

【Interactive Playground】Experience Data Aggregation by Running SQL in Your Browser!

Now it's time to turn knowledge into a skill! Copy the entire HTML code below, save it as a file like sql_aggregate_test.html, and open it in your browser. Your own personal SQL environment will launch, pre-loaded with the sales table we've been using in this article.

Try changing `SUM` to `AVG`, or adding different columns to the `GROUP BY` clause. Experiment freely and see for yourself how data is aggregated and transformed into meaningful information!

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL Aggregate Functions Online Playground</title>
  <style>
    body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif; line-height: 1.7; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    h1 { color: #2980b9; }
    textarea { width: 100%; height: 220px; font-family: "SF Mono", "Consolas", monospace; font-size: 16px; padding: 12px; border: 1px solid #ccc; border-radius: 6px; box-sizing: border-box; margin-bottom: 1rem; }
    button { background-color: #3498db; color: white; border: none; padding: 12px 22px; font-size: 16px; border-radius: 6px; cursor: pointer; transition: background-color 0.2s; }
    button:hover { background-color: #2980b9; }
    button:disabled { background-color: #bdc3c7; cursor: not-allowed; }
    #result-container { margin-top: 2rem; border: 1px solid #ddd; padding: 1rem; border-radius: 6px; background: #fdfdfd; min-height: 50px; }
    #error-message { color: #e74c3c; font-weight: bold; }
    table { border-collapse: collapse; width: 100%; margin-top: 1rem; }
    th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
    th { background-color: #f2f2f2; }
    tr:nth-child(even) { background-color: #f9f9f9; }
  </style>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
</head>
<body>

  <h1>Let's Try SQL!</h1>
  <p>Enter an SQL statement in the textarea below and click the "Execute" button. Let's try various aggregations!</p>

  <textarea id="sql-input">-- Let's group by product name and calculate total revenue and total quantity for each product
SELECT
  product_name,
  SUM(price * quantity) AS total_revenue,
  SUM(quantity) AS total_quantity
FROM
  sales
GROUP BY
  product_name
ORDER BY
  total_revenue DESC;
  </textarea>
  
  <button id="execute-btn">Execute</button>
  
  <div id="result-container">
    <p id="error-message"></p>
    <div id="result-output"></div>
  </div>

  <script>
    const sqlInput = document.getElementById('sql-input');
    const executeBtn = document.getElementById('execute-btn');
    const errorMsg = document.getElementById('error-message');
    const resultOutput = document.getElementById('result-output');

    let db;

    async function initDb() {
      executeBtn.disabled = true;
      executeBtn.textContent = 'DB Initializing...';
      try {
        const SQL = await initSqlJs({
          locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
        });
        db = new SQL.Database();
        
        const setupSql = `
          DROP TABLE IF EXISTS sales;
          CREATE TABLE sales (id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, price INTEGER NOT NULL, quantity INTEGER NOT NULL, sale_date DATE NOT NULL);
          INSERT INTO sales (id, product_name, category, price, quantity, sale_date) VALUES
          (1, 'Amazing Ballpoint Pen', 'Stationery', 150, 10, '2025-07-01'),
          (2, 'Magic Notebook', 'Stationery', 300, 5, '2025-07-01'),
          (3, 'Ultimate Rice Cooker', 'Appliances', 25000, 2, '2025-07-02'),
          (4, 'Miracle Apple', 'Food', 500, 20, '2025-07-03'),
          (5, 'USB-C Hub', 'PC Peripherals', 4000, 3, '2025-07-03'),
          (6, 'Traditional Brush', 'Stationery', 1200, 8, '2025-07-04'),
          (7, 'Premium Chocolate', 'Food', 3500, 5, '2025-07-05'),
          (8, 'Amazing Ballpoint Pen', 'Stationery', 150, 15, '2025-07-05');
        `;
        db.run(setupSql);
        
        executeBtn.disabled = false;
        executeBtn.textContent = 'Execute';
        resultOutput.innerHTML = '<p>Ready! Feel free to try out your own SQL queries.</p>';

      } catch (err) {
        errorMsg.textContent = 'Failed to initialize database: ' + err.message;
        console.error(err);
      }
    }

    function executeSql() {
      if (!db) return;
      
      const sql = sqlInput.value;
      errorMsg.textContent = '';
      resultOutput.innerHTML = '';

      try {
        const results = db.exec(sql);
        if (results.length === 0) {
          resultOutput.innerHTML = '<p>Query executed successfully, but no result set was returned.</p>';
          return;
        }
        
        results.forEach(result => {
          const table = document.createElement('table');
          const thead = document.createElement('thead');
          const tbody = document.createElement('tbody');
          
          const headerRow = document.createElement('tr');
          result.columns.forEach(colName => {
            const th = document.createElement('th');
            th.textContent = colName;
            headerRow.appendChild(th);
          });
          thead.appendChild(headerRow);
          
          result.values.forEach(row => {
            const bodyRow = document.createElement('tr');
            row.forEach(cellValue => {
              const td = document.createElement('td');
              td.textContent = cellValue === null ? 'NULL' : (typeof cellValue === 'number' ? cellValue.toLocaleString() : cellValue);
              bodyRow.appendChild(td);
            });
            tbody.appendChild(bodyRow);
          });
          
          table.appendChild(thead);
          table.appendChild(tbody);
          resultOutput.appendChild(table);
        });

      } catch (err) {
        errorMsg.textContent = 'SQL Error: ' + err.message;
        console.error(err);
      }
    }

    executeBtn.addEventListener('click', executeSql);
    
    initDb();
  </script>
</body>
</html>

Conclusion

In this article, we've learned about the four fundamental aggregate functions for data analysis: `SUM()`, `AVG()`, `MAX()`, and `MIN()`.

These aggregate functions are the first step not just in retrieving data, but in gaining meaningful insights from it. They are incredibly powerful tools for a web creator, useful for everything from business decision-making to identifying areas for site improvement. We encourage you to master these functions and experience the joy of conversing with your data!

Related Templates