Aggregate functions (COUNT, SUM, AVG, MAX, MIN) in DBMS Theory - Mini Project: Build & Apply

Aggregate functions (COUNT, SUM, AVG, MAX, MIN)

📖 Scenario: You are working with a simple sales database that records sales transactions. Each transaction has a product name, quantity sold, and price per unit.You want to learn how to use SQL aggregate functions to get useful summaries from this data.

🎯 Goal: Build SQL queries step-by-step to calculate total sales, average quantity sold, maximum price, minimum price, and count of transactions.

📋 What You'll Learn

Create a table called sales with columns product, quantity, and price

Insert the exact rows specified into the sales table

Write SQL queries using aggregate functions COUNT, SUM, AVG, MAX, and MIN

Use the exact column names and table name as specified

💡 Why This Matters

🌍 Real World

Businesses use aggregate functions to summarize sales data, track performance, and make decisions based on totals, averages, and extremes.

💼 Career

Knowing SQL aggregate functions is essential for data analysts, database administrators, and developers working with data reporting and business intelligence.

1

DATA SETUP: Create the sales table and insert data

Write SQL statements to create a table called sales with columns product (text), quantity (integer), and price (decimal). Then insert these exact rows: ('Apple', 10, 0.5), ('Banana', 5, 0.3), ('Orange', 8, 0.7), ('Apple', 3, 0.5), ('Banana', 7, 0.3).

DBMS Theory

-- Create the sales table and insert the rows below
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add data.

2

CONFIGURATION: Write a query to count total transactions

Write a SQL query that uses the COUNT aggregate function to count the total number of rows (transactions) in the sales table. Name the result column total_transactions.

DBMS Theory

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price DECIMAL(5,2)
);

INSERT INTO sales (product, quantity, price) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Orange', 8, 0.7),
('Apple', 3, 0.5),
('Banana', 7, 0.3);

-- Write a query to count total transactions
-- Your code here

Hint

Use COUNT(*) to count all rows and AS to name the output column.

3

CORE LOGIC: Calculate total quantity sold and average price

Write a SQL query that uses SUM(quantity) to find the total quantity sold and AVG(price) to find the average price from the sales table. Name the columns total_quantity and average_price respectively.

DBMS Theory

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price DECIMAL(5,2)
);

INSERT INTO sales (product, quantity, price) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Orange', 8, 0.7),
('Apple', 3, 0.5),
('Banana', 7, 0.3);

SELECT COUNT(*) AS total_transactions FROM sales;

-- Write a query to get total quantity and average price
-- Your code here

Hint

Use SUM() and AVG() together in the SELECT clause.

4

COMPLETION: Find maximum and minimum price in sales

Write a SQL query that uses MAX(price) to find the highest price and MIN(price) to find the lowest price in the sales table. Name the columns max_price and min_price respectively.

DBMS Theory

CREATE TABLE sales (
  product TEXT,
  quantity INTEGER,
  price DECIMAL(5,2)
);

INSERT INTO sales (product, quantity, price) VALUES
('Apple', 10, 0.5),
('Banana', 5, 0.3),
('Orange', 8, 0.7),
('Apple', 3, 0.5),
('Banana', 7, 0.3);

SELECT COUNT(*) AS total_transactions FROM sales;

SELECT SUM(quantity) AS total_quantity, AVG(price) AS average_price FROM sales;

-- Write a query to get max and min price
-- Your code here

Hint

Use MAX() and MIN() in the SELECT clause to find highest and lowest values.

Practice

1. Which aggregate function in SQL is used to find the total number of rows in a table?

easy

A. COUNT

B. SUM

C. AVG

D. MAX

Solution

1. Step 1: Understand the purpose of each aggregate function

   COUNT counts the number of rows, SUM adds values, AVG calculates average, and MAX finds the highest value.

2. Step 2: Identify the function that counts rows

   The function that counts rows is COUNT.

3. Final Answer:

   COUNT -> Option A

4. Quick Check:

   Counting rows = COUNT [OK]

Hint: COUNT counts rows, SUM adds numbers [OK]

Common Mistakes:

• Confusing SUM with COUNT
• Using AVG to count rows
• Thinking MAX counts rows

2. Which of the following is the correct syntax to find the average salary from a table named Employees?

easy

A. SELECT SUM(salary) / COUNT(*) FROM Employees;

B. SELECT AVG(salary) FROM Employees;

C. SELECT MEAN(salary) FROM Employees;

D. SELECT AVERAGE(salary) FROM Employees;

Solution

1. Step 1: Recall the standard SQL function for average

   The standard SQL function to calculate average is AVG().

2. Step 2: Check the syntax correctness

   SELECT AVG(salary) FROM Employees; is the correct syntax. Other options use non-standard functions or expressions.

3. Final Answer:

   SELECT AVG(salary) FROM Employees; -> Option B

4. Quick Check:

   Average function = AVG() [OK]

Hint: Use AVG() for average, not AVERAGE or MEAN [OK]

Common Mistakes:

• Using non-standard function names like AVERAGE or MEAN
• Trying to calculate average manually with SUM/COUNT
• Missing parentheses in function call

3. Consider the table Sales with a column amount having values: 100, 200, 300, 400. What will be the result of the query SELECT MAX(amount) FROM Sales;?

medium

A. 400

B. 100

C. 250

D. 1000

Solution

1. Step 1: Identify the values in the column

   The amount column has values 100, 200, 300, and 400.

2. Step 2: Apply the MAX function

   MAX(amount) returns the highest value, which is 400.

3. Final Answer:

   400 -> Option A

4. Quick Check:

   Maximum of 100,200,300,400 = 400 [OK]

Hint: MAX returns highest value in the column [OK]

Common Mistakes:

• Choosing SUM or AVG instead of MAX
• Picking the lowest value by mistake
• Adding values instead of finding max

4. What is wrong with this SQL query?
SELECT SUM(price), COUNT FROM Products;

medium

A. Products table does not exist

B. SUM cannot be used with price column

C. Missing WHERE clause

D. COUNT requires parentheses with a column or *

Solution

1. Step 1: Analyze the use of COUNT function

   The COUNT function must be called with parentheses, like COUNT(*) or COUNT(column_name).

2. Step 2: Identify the syntax error

   The query uses COUNT without parentheses, which is invalid syntax.

3. Final Answer:

   COUNT requires parentheses with a column or * -> Option D

4. Quick Check:

   COUNT() needs parentheses [OK]

Hint: Always use COUNT() with parentheses [OK]

Common Mistakes:

• Writing COUNT without parentheses
• Assuming SUM can't be used on price
• Thinking WHERE clause is mandatory

5. You have a table Orders with columns order_id, customer_id, and amount. You want to find the average order amount for each customer. Which SQL query correctly uses aggregate functions to achieve this?

hard

A. SELECT AVG(amount) FROM Orders WHERE customer_id;

B. SELECT customer_id, SUM(amount) FROM Orders;

C. SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id;

D. SELECT customer_id, COUNT(amount) FROM Orders GROUP BY order_id;

Solution

1. Step 1: Understand the requirement

   We want the average order amount per customer, so we need to group data by customer_id and calculate average amount.

2. Step 2: Check each query's correctness

   SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id; groups by customer_id and uses AVG(amount), which is correct. SELECT customer_id, SUM(amount) FROM Orders; sums amounts but does not group, so it gives total sum only. SELECT AVG(amount) FROM Orders WHERE customer_id; has invalid WHERE clause usage. SELECT customer_id, COUNT(amount) FROM Orders GROUP BY order_id; groups by order_id, which is wrong for per customer aggregation.

3. Final Answer:

   SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id; -> Option C

4. Quick Check:

   Group by customer_id + AVG(amount) [OK]

Hint: Use GROUP BY with AVG for per-group averages [OK]

Common Mistakes:

• Not using GROUP BY when aggregating per group
• Using SUM instead of AVG for average
• Incorrect WHERE clause instead of GROUP BY