SQL AVG function
SQL AVG function – Complete Guide
In SQL AVG function is an aggregate function used to calculate the average (mean) of numeric values in a column.
1. Basic Syntax (Beginner)
- Works only on numeric columns
2. Simple Example
Table: employees
| id | name | salary |
|---|---|---|
| 1 | Amit | 50000 |
| 2 | Ravi | 45000 |
| 3 | Neha | 55000 |
Query:
Output:
3. AVG with WHERE Clause
Calculate average salary of IT department employees.
- Filters rows before calculating average
4. AVG with GROUP BY (Very Important)
Average salary per department:
- Interview favorite
5. AVG with Multiple Columns (Not Allowed)
This is invalid:
Correct way:
6. AVG Ignores NULL Values
Example:
| salary |
|---|
| 50000 |
| NULL |
| 60000 |
Result:
NULL values are ignored, not counted as zero
7. AVG with DISTINCT
- Calculates average of unique values only
8. AVG with HAVING Clause
Find departments where average salary > 50000:
WHERE→ filters rowsHAVING→ filters groups
9. AVG with Decimal Result (Type Casting)
Some databases return integer results if values are integers.
Safe approach:
or
10. AVG with JOIN (Real-World Example)
- Very common in real projects
11. AVG vs SUM / COUNT
| Function | Purpose |
|---|---|
SUM() | Total |
COUNT() | Number of rows |
AVG() | Mean value |
Formula used internally:
12. Common Mistakes
- Using
AVG()on text column - Forgetting
GROUP BY UsingWHEREinstead ofHAVINGExpecting NULL to be counted
13. Interview Questions (Must Know)
Q1: Does AVG() count NULL values?
No
Q2: Can we use AVG() with WHERE?
Yes
Q3: Difference between WHERE and HAVING with AVG()?WHERE filters rows, HAVING filters groups
Q4: Can AVG() return decimal values?
Yes
