AVG function in SQL - Mini Project: Build & Apply

Calculate Average Sales Using AVG Function

📖 Scenario: You work at a small store that keeps track of daily sales in a database table. You want to find out the average sales amount to understand how well the store is doing on average each day.

🎯 Goal: Build a SQL query that calculates the average sales amount from the sales data using the AVG function.

📋 What You'll Learn

Create a table called sales with columns day and amount.

Insert the exact sales data for 5 days into the sales table.

Write a SQL query that uses the AVG function to find the average sales amount.

Complete the query to return the average sales with the column name average_sales.

💡 Why This Matters

🌍 Real World

Calculating averages is common in business to understand typical performance, such as average sales, average customer ratings, or average expenses.

💼 Career

Knowing how to use the AVG function and filter data with WHERE is essential for data analysts, database administrators, and anyone working with data to generate meaningful insights.

1

Create the sales table and insert data

Create a table called sales with two columns: day as INTEGER and amount as INTEGER. Then insert these exact rows into the sales table: (1, 100), (2, 150), (3, 200), (4, 130), (5, 170).

SQL

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE TABLE to make the table and INSERT INTO to add the rows.

2

Set up the query to calculate average

Write a SQL query starting with SELECT that will calculate the average of the amount column from the sales table. Use the AVG function and alias the result as average_sales.

SQL

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales (day, amount) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

-- Write the SELECT query using AVG function
-- Your code here

Hint

Use SELECT AVG(amount) AS average_sales FROM sales; to get the average sales.

3

Add a WHERE clause to filter days

Modify the existing query to calculate the average sales amount only for days greater than 2. Add a WHERE clause with the condition day > 2.

SQL

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales (day, amount) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

-- Add WHERE clause to filter days > 2
-- Your code here

Hint

Use WHERE day > 2 to filter the rows before averaging.

4

Complete the query with ORDER BY and LIMIT

Add ORDER BY average_sales DESC and LIMIT 1 to the query to show the highest average sales first and limit the output to one row.

SQL

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales (day, amount) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

-- Add ORDER BY and LIMIT to complete the query
-- Your code here

Hint

Use ORDER BY average_sales DESC LIMIT 1 to sort and limit the results.

Practice

1. What does the SQL AVG() function do?

easy

A. Calculates the average value of a numeric column

B. Counts the number of rows in a table

C. Finds the maximum value in a column

D. Returns the sum of all values in a column

Solution

1. Step 1: Understand the purpose of AVG()

   The AVG() function is designed to calculate the average (mean) of numeric values in a column.

2. Step 2: Compare with other aggregate functions

   Unlike COUNT(), MAX(), or SUM(), AVG() specifically returns the average value.

3. Final Answer:

   Calculates the average value of a numeric column -> Option A

4. Quick Check:

   AVG() = average calculation [OK]

Hint: AVG() always returns the mean of numbers, not counts or sums [OK]

Common Mistakes:

• Confusing AVG() with COUNT()
• Thinking AVG() sums values without dividing
• Assuming AVG() works on non-numeric columns

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

easy

A. SELECT AVG salary FROM Employees;

B. SELECT AVG(salary) FROM Employees;

C. SELECT AVERAGE(salary) FROM Employees;

D. SELECT salary AVG() FROM Employees;

Solution

1. Step 1: Recall correct AVG() syntax

   The AVG() function requires parentheses around the column name: AVG(column_name).

2. Step 2: Check each option

   Correct syntax uses AVG(salary) with parentheses. Missing parentheses, AVERAGE(salary), or salary AVG() cause syntax errors.

3. Final Answer:

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

4. Quick Check:

   AVG(column) uses parentheses [OK]

Hint: AVG() always needs parentheses around the column name [OK]

Common Mistakes:

• Omitting parentheses in AVG()
• Using wrong function name like AVERAGE()
• Placing AVG() after column name incorrectly

3. Given the table Scores with values:
Score
90
80
NULL
70
What will the query SELECT AVG(Score) FROM Scores; return?

medium

A. 240

B. NULL

C. 80

D. 75

Solution

1. Step 1: Identify values considered by AVG()

   AVG() ignores NULL values, so it averages 90, 80, and 70.

2. Step 2: Calculate the average

   (90 + 80 + 70) / 3 = 240 / 3 = 80.

3. Final Answer:

   80 -> Option C

4. Quick Check:

   AVG ignores NULL, average = 80 [OK]

Hint: AVG() skips NULLs automatically when averaging [OK]

Common Mistakes:

• Including NULL as zero in average
• Returning NULL if any NULL exists
• Summing values without dividing

4. Consider this query:
SELECT AVG(price) FROM Products WHERE price > 0;
It returns NULL even though there are products with price 0 and above. What is the likely problem?

medium

A. The WHERE clause excludes all rows because price > 0 filters out zero prices

B. AVG() cannot be used with WHERE clause

C. The price column contains only NULL values

D. AVG() requires GROUP BY to work

Solution

1. Step 1: Analyze the WHERE clause condition

   The condition price > 0 excludes prices equal to zero, so only prices greater than zero are included.

2. Step 2: Consider data and NULL result

   If no prices are greater than zero, the filtered set is empty, so AVG() returns NULL.

3. Final Answer:

   The WHERE clause excludes all rows because price > 0 filters out zero prices -> Option A

4. Quick Check:

   Empty filtered rows cause AVG() to return NULL [OK]

Hint: Check WHERE filters exclude all rows causing NULL AVG() [OK]

Common Mistakes:

• Thinking AVG() can't use WHERE
• Assuming AVG() needs GROUP BY always
• Ignoring that empty sets return NULL

5. You have a table Sales with columns Region and Amount. How do you write a query to find the average sales amount per region, excluding regions with no sales?

hard

A. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region WHERE Amount IS NOT NULL;

B. SELECT Region, AVG(Amount) FROM Sales WHERE Amount > 0;

C. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region WHERE Amount > 0;

D. SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL;

Solution

1. Step 1: Group sales by region

   Use GROUP BY Region to calculate average per region.

2. Step 2: Exclude regions with no sales

   Regions with no sales have AVG(Amount) as NULL, so use HAVING AVG(Amount) IS NOT NULL to filter them out.

3. Final Answer:

   SELECT Region, AVG(Amount) FROM Sales GROUP BY Region HAVING AVG(Amount) IS NOT NULL; -> Option D

4. Quick Check:

   GROUP BY + HAVING filters NULL averages [OK]

Hint: Use HAVING to exclude groups with NULL averages [OK]

Common Mistakes:

• Using WHERE after GROUP BY (invalid syntax)
• Not filtering NULL averages with HAVING
• Filtering rows before grouping instead of after