Question

Enlist four aggregate functions.

Answer 1

Of course. Here are four fundamental aggregate functions used in SQL, along with their definitions 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 |
| :--- | :--- | :--- | :--- |
| 1 | Laptop | Electronics | 1200.00 |
| 2 | Mouse | Electronics | 25.00 |
| 3 | T-Shirt | Apparel | 20.00 |
| 4 | Jeans | Apparel | 75.00 |
| 5 | Keyboard | Electronics | 70.00 |
| 6 | Coffee Mug| Kitchen | NULL |

---

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 NULLs.
  • 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.
  `sql
  SELECT COUNT(*)
  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)
  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 value of all products in stock.
  `sql
  SELECT SUM(Price)
  FROM Products;
  `
  Result: 1390.00 (1200 + 25 + 20 + 75 + 70)

• Example 2: Calculate the total value of products in the 'Electronics' category.
  `sql
  SELECT SUM(Price)
  FROM Products
  WHERE Category = 'Electronics';
  `
  Result: 1295.00 (1200 + 25 + 70)

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.
  (This will be the sum of prices divided by the count of non-NULL prices: 1390.00 / 5).
  `sql
  SELECT AVG(Price)
  FROM Products;
  `
  Result: 278.00

• Example 2: Find the average price of products in the 'Apparel' category.
  `sql
  SELECT AVG(Price)
  FROM Products
  WHERE Category = 'Apparel';
  `
  Result: 47.50 ((20 + 75) / 2)

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.
• Syntax: MAX(column_name)

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

• Example 2: Find the most expensive product in each category.
  This example uses GROUP BY to show the power of aggregate functions.
  `sql
  SELECT Category, MAX(Price)
  FROM Products
  GROUP BY Category;
  `
  Result:
  | Category | MAX(Price) |
  | :--- | :--- |
  | Electronics | 1200.00 |
  | Apparel | 75.00 |
  | Kitchen | NULL |

(Note: MIN() is the counterpart to MAX() and finds the smallest value in a column.)