Syntax:

SELECT AVG(column_name) 
FROM table_name  
WHERE
``````html
condition;

Illustration:

SELECT AVG(cost) AS Avg_High_Cost  
FROM dvc_mnt  
WHERE cost > 1000;

Result:

Clarification: “In this case, the WHERE clause narrows down rows that have a cost exceeding 1000, and subsequently, the AVG() function ascertains the average of those costs.

Employing the AVG() Function with the GROUP BY Clause in SQL

The AVG() function in conjunction with GROUP BY calculates the average for each collection of records sharing a common attribute.

Format:

SELECT group_column, AVG(column_name) 
FROM table_name  
GROUP BY group_column;

Illustration:

SELECT city, AVG(cost) AS Avg_Cost_By_City  
FROM dvc_mnt  
GROUP BY city;

Result:

Clarification: In this scenario, we categorize by the city column and compute AVG(cost) for every category. For instance, Trichy has a single entry for 3000.00; consequently, its average stands at 3000.00.

Applying the AVG() Function with the HAVING Clause in SQL

The HAVING clause refines records that have already been grouped based on the output of the AVG() function.

Format:

SELECT group_column, AVG(column_name) 
FROM table_name  
GROUP BY group_column  
HAVING AVG(column_name) condition;

Illustration:

SELECT city, AVG(cost) AS Avg_Cost  
FROM dvc_mnt  
GROUP BY city  
HAVING AVG(cost) > 1000;

Result:

Clarification: In this case, the HAVING clause eliminates grouped outcomes where the average cost is less than or equal to 1000. Thus, the resulting data includes only towns with an average cost of 1000 or more.

Practical Illustration

Electricity Consumption Monitor: The local power authority aims to evaluate the average monthly power consumption per city to facilitate effective future energy allocations.

Illustration:

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;

Result:

Clarification: In this instance, the power consumption is compiled by city to compute the average units utilized. For example, Nagpur has consumption values of 320 and 280 units, leading to (320 + 280) / 2 = 300.00.

Frequent Errors to Avoid

• Applying AVG() without GROUP BY Clause: Employ GROUP BY with AVG() when you intend to compute averages per category. Otherwise, AVG() might return a single average for the entire dataset.
• Misusing HAVING: HAVING should be utilized to filter aggregated data post-grouping, while WHERE narrows down rows beforehand.
• Using an inappropriate field type: The AVG() function generates an error if used on a non-numeric field; SQL engines do not attempt to auto-convert string or text types.
• Believing AVG() means distinct: AVG() does not exclude duplicate entries unless explicitly instructed to do so using DISTINCT.

Optimal Practices

• Utilize AVG(DISTINCT column) when required: To remove duplicates from the average, make use of AVG(DISTINCT column).
• Always manage NULLs: Incorporate IS NOT NULL in the WHERE clause to filter out NULL values, since AVG() by default ignores them, which could influence your outcomes.
• Adopt meaningful aliases for clarity: Assign descriptive aliases, e.g., AS Avg_Salary, to enhance comprehension of the results.
• Combine with the GROUP BY clause when needed: When implementing the GROUP BY statement on the data, pair AVG() function with GROUP BY for enhanced insights about groups (e.g., departments, cities).

Kickstart Your SQL Adventure – For Free!

Learn to craft powerful queries and manage databases with our beginner-friendly free SQL course.

Explore Program

Conclusion

The AVG() function serves as a practical aggregate instrument in SQL aimed at determining the average of numeric columns. It can also be utilized effectively alongside clauses like WHERE, GROUP BY, and HAVING to filter and organize data. Furthermore, with meticulous handling of NULL values and grouping similar entities with GROUP BY, the AVG() function becomes a fundamental necessity for reporting, analytics, and decision-making in real-life applications. In this article, you have acquired a comprehensive understanding of the SQL AVG() function.

Elevate your abilities by enrolling in the SQL Training Course today and gain practical experience. Additionally, prepare for job interviews with SQL interview questions, crafted by industry specialists.

SQL AVG() Function – FAQs

The post SQL AVG() Function appeared first on Intellipaat Blog.

“`