SQL — Avg

Basic Syntax

SELECT AVG(column_name) AS average_value
FROM table_name;

Simple Example

SELECT AVG(salary) AS average_salary
FROM employees;

AVG with ROUND

SELECT ROUND(AVG(salary), 2) AS average_salary
FROM employees;

AVG with WHERE

Filter rows before averaging:

SELECT AVG(salary) AS avg_salary
FROM employees
WHERE department = 'Engineering';

AVG with GROUP BY

Average per category:

SELECT
  department,
  ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

AVG with HAVING

Filter groups based on their average:

SELECT
  department,
  ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

AVG with JOIN

SELECT
  d.department_name,
  ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name;

AVG Ignores NULLs

CREATE TABLE scores (student_id INT, score INT);
INSERT INTO scores VALUES (1, 90), (2, NULL), (3, 85);

SELECT AVG(score) FROM scores;  -- 87.5 (NULL is ignored, not counted as 0)

AVG with CASE

Conditional averaging:

SELECT
  AVG(CASE WHEN department = 'Sales' THEN salary END) AS avg_sales,
  AVG(CASE WHEN department = 'Engineering' THEN salary END) AS avg_engineering
FROM employees;

Weighted Average

-- Calculate weighted average price
SELECT
  SUM(price * quantity) / SUM(quantity) AS weighted_avg_price
FROM products;

Practical Example

-- Monthly average order value
SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  ROUND(AVG(total), 2) AS avg_order_value,
  COUNT(*) AS order_count
FROM orders
GROUP BY month
ORDER BY month DESC;

NULL vs Zero

-- NULL rows are excluded from the average
SELECT AVG(salary) FROM employees; -- avg of non-NULL salaries

-- If you want NULL to count as 0:
SELECT AVG(COALESCE(salary, 0)) FROM employees;

Practice

1. Calculate the average price of products in each category
2. Find departments where the average salary exceeds $75,000
3. Calculate the monthly average order count over the past year
4. Find the weighted average rating for products with multiple reviews