Question

Explain any four aggregate functions with example.

Answer 1

Of course. Here are four of the most common and essential aggregate functions used in SQL, complete with their definitions, syntax, and practical examples.

---

Introduction to Aggregate Functions

An aggregate function in SQL performs a calculation on a set of rows and returns a single, summary value. These functions are often used with the GROUP BY clause to provide summary statistics for different subgroups of data.

To illustrate these functions, let's use the following sample Products table:

Products Table:
| ProductID | ProductName | Category | Price | StockQuantity |
| :--- | :--- | :--- | :--- | :--- |
| 1 | Laptop | Electronics | 1200.00 | 10 |
| 2 | Mouse | Electronics | 25.00 | 150 |
| 3 | T-Shirt | Apparel | 20.00 | 200 |
| 4 | Jeans | Apparel | 75.00 | 80 |
| 5 | Keyboard | Electronics | 70.00 | 120 |
| 6 | Coffee Mug| Kitchen | NULL | 300 |

---

Four Common Aggregate Functions

1. COUNT()

• Purpose: The COUNT() function returns the number of rows that match a specified criterion.
• Common Forms:
  • COUNT(*): Counts all rows in the group, including duplicates and NULL values.
  • COUNT(column_name): Counts the number of non-NULL values in a specific column.
  • COUNT(DISTINCT column_name): Counts the number of unique, non-NULL values in a column.

• Example 1: Count all products in the table.
  `sql
  SELECT COUNT(*) AS TotalProducts
  FROM Products;
  `
  Result: 6

• Example 2: Count how many products have a price listed.
  (This will ignore the "Coffee Mug," which has a NULL price).
  `sql
  SELECT COUNT(Price) AS ProductsWithPrice
  FROM Products;
  `
  Result: 5

2. SUM()

• Purpose: The SUM() function returns the total sum of a numeric column. It ignores NULL values in its calculation.
• Syntax: SUM(column_name)

• Example 1: Calculate the total number of items in stock across all products.
  `sql
  SELECT SUM(StockQuantity) AS TotalItemsInStock
  FROM Products;
  `
  Result: 860 (10 + 150 + 200 + 80 + 120 + 300)

• Example 2: Calculate the total value of all products in the 'Electronics' category.
  `sql
  SELECT SUM(Price) AS TotalElectronicsValue
  FROM Products
  WHERE Category = 'Electronics';
  `
  Result: 1295.00 (1200.00 + 25.00 + 70.00)

3. AVG()

• Purpose: The AVG() function calculates the average value of a numeric column. Like SUM(), it ignores NULL values.
• Syntax: AVG(column_name)

• Example 1: Find the average price of all products that have a price.
  (This will be the sum of prices divided by the count of non-NULL prices: 1390.00 / 5).
  `sql
  SELECT AVG(Price) AS AveragePrice
  FROM Products;
  `
  Result: 278.00

• Example 2: Find the average stock quantity for each product category.
  This example uses GROUP BY to show the power of aggregate functions.
  `sql
  SELECT Category, AVG(StockQuantity) AS AvgStock
  FROM Products
  GROUP BY Category;
  `
  Result:
  | Category | AvgStock |
  | :--- | :--- |
  | Electronics | 93.33 | (Calculated as (10+150+120)/3)
  | Apparel | 140.00 | (Calculated as (200+80)/2)
  | Kitchen | 300.00 | (Calculated as 300/1)

4. MAX()

• Purpose: The MAX() function returns the largest (maximum) value in a set of values. It can be used with numeric, text, or date columns. (MIN() is its counterpart, which finds the smallest value).
• Syntax: MAX(column_name)

• Example 1: Find the price of the most expensive product.
  `sql
  SELECT MAX(Price) AS MostExpensiveProductPrice
  FROM Products;
  `
  Result: 1200.00

• Example 2: Find the most expensive product in each category.
  `sql
  SELECT Category, MAX(Price) AS HighestPrice
  FROM Products
  GROUP BY Category;
  `
  Result:
  | Category | HighestPrice |
  | :--- | :--- |
  | Electronics | 1200.00 |
  | Apparel | 75.00 |
  | Kitchen | NULL |