Avg Function In Sql in SQL
? AVG() Function in SQL
The AVG() function calculates the average (arithmetic mean) of the values in a numeric column.
? Syntax
SELECT AVG(column_name)FROM table_nameWHERE condition;Works on numeric columns (
INT,FLOAT,DECIMAL, etc.).Ignores
NULLvalues automatically.Returns the average as a float or appropriate numeric type.
? Example Table: sales
| id | product | amount |
|---|---|---|
| 1 | Laptop | 1000.00 |
| 2 | Keyboard | 150.00 |
| 3 | Mouse | 50.00 |
| 4 | Laptop | 1200.00 |
| 5 | Mouse | NULL |
? Example Queries
1. Average of entire column
SELECT AVG(amount) AS avg_amountFROM sales;Result: (1000 + 150 + 50 + 1200) / 4 = 600 (ignores NULL)
2. Average by product
SELECT product, AVG(amount) AS avg_amountFROM salesGROUP BY product;| product | avg_amount |
|---|---|
| Laptop | 1100.00 |
| Keyboard | 150.00 |
| Mouse | 50.00 |
3. Average with condition
SELECT AVG(amount) AS avg_amount_highFROM salesWHERE amount > 100;Calculates average for amounts greater than 100.
?? Notes
AVG()ignoresNULLvalues.Use with
GROUP BYto get averages per group.Can be combined with other aggregate functions like
SUM(),COUNT(), etc.
Let me know if you want examples combining AVG() with HAVING or window functions!
