The AVG() function is widely used to analyze trends and assess performance over time. It calculates the mean of a dataset, making it helpful for summarizing data. For example, you can use the AVG() function to find the average salary of employees, the average marks of students, or even the average sales in a quarter. In general, helps show trends, makes comparisons, and provides insights for data-driven decisions. In this blog, we will explore the SQL AVG() function in detail with examples.
Table of Contents:
What is the AVG() function in SQL?
AVG() is an aggregate function in SQL that returns the arithmetic mean of all values in a specified numeric column of a table. It sums all non-NULL values in the specified numeric column and divides the total by the number of non-NULL entries. It is used to summarize data such as average salary, average marks, and average sales.
Why do we need the AVG() function in SQL?
The AVG() function is significant in SQL for summarizing and assessing substantial sets of data, given that it allows for comprehension of the average or center of numeric data; rather than tediously averaging with math, AVG can be easily utilized in a SQL query and works efficiently with datasets containing thousands or even millions of rows.
- Data Summarization: Quickly determine the average values in large organizational datasets, it could be the average salary or average product rating, etc.
- Performance Analysis: Assess performance indicators like average test scores or average monthly sales.
- Decision Making: Decision making for organizations and analysts can be much easier with the trend insights of averages, such as determining under-performing departments by average revenue.
Master SQL: Empower Your Data Skills Today!
Unlock the power of databases with hands-on SQL training and become a data-driven professional
Before moving forward with the AVG() function, we need to create a table for Device Maintenance.
CREATE TABLE dvc_mnt (
tckt_id INT PRIMARY KEY,
dvc_owner VARCHAR(60),
city VARCHAR(50),
dvc_type VARCHAR(40),
issue VARCHAR(100),
cost DECIMAL(10,2),
tch_name VARCHAR(50)
);
INSERT INTO dvc_mnt (
tckt_id, dvc_owner, city, dvc_type, issue, cost, tch_name
) VALUES
(101, 'Sanjay', 'Mysuru', 'Router', 'Frequent disconnections', 750.00, 'Manoj'),
(102, 'Ritika', 'Kochi', 'Smartphone', 'Battery overheating', 1200.00, 'Neeraj'),
(103, 'Aravind', 'Jodhpur', 'Printer', 'Paper jam issue', 950.00, 'Sowmya'),
(104, 'Ishita', 'Trichy', 'Laptop', 'Screen flickering', 3000.00, 'Karthik');
Select * from dvc_mnt;
This is how the table looks once it is created and inserted with values.
Syntax and Example of AVG() Function in SQL
Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Example:
SELECT AVG(cost) AS Avg_Maintenance_Cost
FROM dvc_mnt;
Output:
Explanation: Here, the AVG(cost) function first identifies the cost for every row, builds a summation of all of the items in the cost column, and calculates the average cost, using our summation total divided by the number of rows.
Examples of using the AVG() function with Different Clauses in SQL
The AVG() function can be combined with various SQL clauses like WHERE, GROUP BY, and HAVING to calculate meaningful averages in different scenarios.
Using the AVG() function with the WHERE Clause in SQL
The AVG() function with a WHERE clause finds the average of filtered rows that meet the specific conditions.
Syntax:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
Example:
SELECT AVG(cost) AS Avg_High_Cost
FROM dvc_mnt
WHERE cost > 1000;
Output:
Explanation: “Here, the WHERE clause filters rows that have a cost value greater than 1000, and then the AVG() function calculates the average of those values.
Using the AVG() Function with the GROUP BY Clause in SQL
The AVG() function with GROUP BY finds the average for every group of records with a common value.
Syntax:
SELECT group_column, AVG(column_name)
FROM table_name
GROUP BY group_column;
Example:
SELECT city, AVG(cost) AS Avg_Cost_By_City
FROM dvc_mnt
GROUP BY city;
Output:
Explanation: Here, we are grouping by the city column and calculating AVG(cost) for each group. Trichy’s has one row for 3000.00; hence, its average is 3000.00.
Get 100% Hike!
Master Most in Demand Skills Now!
Using the AVG() Function with the HAVING Clause in SQL
The HAVING clause filters records that have already been grouped based on the result of the AVG() function.
Syntax:
SELECT group_column, AVG(column_name)
FROM table_name
GROUP BY group_column
HAVING AVG(column_name) condition;
Example:
SELECT city, AVG(cost) AS Avg_Cost
FROM dvc_mnt
GROUP BY city
HAVING AVG(cost) > 1000;
Output:
Explanation: Here, the HAVING clause removes grouped results in which the average cost is equal to or below 1000. Thus, the result contains only cities with an average cost of 1000 or greater.
Real-world Example
Electricity Usage Monitor: The local electricity board wants to estimate the average monthly electricity usage per city so that it can plan future energy distribution efficiently.
Example:
CREATE TABLE City_Egy (
bill_id INT PRIMARY KEY,
city VARCHAR(50),
cons_name VARCHAR(60),
usg_units INT
);
INSERT INTO City_Egy (bill_id, city, cons_name, usg_units) VALUES
(1, 'Nagpur', 'Rekha', 320),
(2, 'Nagpur', 'Sanjana', 280),
(3, 'Bhopal', 'Sri', 400),
(4, 'Bhopal', 'Rohit', 360),
(5, 'Surat', 'Divya', 290),
(6, 'Surat', 'Amit', 310);
SELECT city, AVG(usg_units) AS Avg_Monthly_Usage
FROM City_Egy
GROUP BY city;
Output:
Explanation: Here, the electricity usage is grouped by city to calculate the average units consumed. For example, Nagpur has values of 320 and 280 units; therefore, (320 + 280) / 2 = 300.00.
Common Mistakes to Avoid
- Using AVG() without GROUP BY Clause: Use GROUP BY with AVG() when you want to calculate averages per group. Otherwise, AVG() can return a single average for the whole dataset.
- Using HAVING incorrectly: HAVING should be used to filter aggregated data after grouping, whereas WHERE filters rows before aggregation
- Using the wrong type of field: The AVG() function throws an error if applied to a non-numeric field; SQL engines do not try to auto-cast string or text types.
- Assuming AVG() means distinct: AVG() does not exclude duplicate entries unless specified to do so using DISTINCT.
Best Practices
- Use AVG(DISTINCT column) when necessary: If you want to eliminate duplicates from the average, you can use AVG(DISTINCT column).
- Always handle NULLs: Use IS NOT NULL in the WHERE clause to exclude NULL values, as AVG() ignores them by default, which could affect your results.
- Use an alias for clarity: Always give meaningful aliases, like AS Avg_Salary, to make the results easier to understand.
- Combine with the GROUP BY clause when necessary: When applying GROUP BY statement to the data, utilize AVG() function with GROUP BY to provide details in regards to groups (e.g. departments, cities).
Kickstart Your SQL Journey – For Free!
Learn how to write powerful queries and manage databases with our beginner-friendly free SQL course.
Conclusion
The AVG() function is a useful aggregate tool in SQL designed to calculate the average of numeric columns. The AVG() function can also be used effectively with clauses such as WHERE, GROUP BY, and HAVING to filter and group data. Additionally, with careful treatment of NULL values and managing like entities in groups identified with GROUP BY, the AVG() function is a basic requirement for reporting, analytics, and decision making in the real world. In this blog, you have gained knowledge on the SQL AVG() function in detail.
Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL interview questions, prepared by industry experts.
SQL AVG() Function – FAQs