PostgreSQL AVG

Basic AVG Syntax

SELECT AVG(salary) FROM employees;

SELECT AVG(score) 
FROM students 
WHERE grade = 'A';

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

SELECT ROUND(AVG(price), 2)
FROM products;

🔹 Basic AVG Example

The AVG function computes the arithmetic mean by summing all values and dividing by the count of non-NULL values. It automatically ignores NULL entries in calculations. This is commonly used for statistical analysis, performance metrics, and finding typical values in your dataset.

-- Sample data: products table
-- product_id | product_name | price
-- 1          | Laptop       | 1200
-- 2          | Mouse        | 25
-- 3          | Keyboard     | 75
-- 4          | Monitor      | 300

SELECT AVG(price) AS average_price
FROM products;

🔹 AVG with WHERE Clause

Combine AVG with WHERE to calculate averages for specific subsets of data. The WHERE clause filters rows before the average is calculated, allowing you to find averages for particular categories, time periods, or conditions. This is useful for comparative analysis and targeted reporting.

-- Calculate average salary for Sales department
SELECT AVG(salary) AS avg_sales_salary
FROM employees
WHERE department = 'Sales';

🔹 AVG with GROUP BY

Use GROUP BY with AVG to calculate separate averages for different groups or categories. Each unique value in the grouped column gets its own average calculation. This is essential for comparing performance across departments, regions, or product categories in analytical reports.

-- Sample data: employees table
-- name     | department  | salary
-- John     | Sales       | 60000
-- Sarah    | Sales       | 70000
-- Mike     | IT          | 80000
-- Lisa     | IT          | 90000

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

🔹 Rounding AVG Results

PostgreSQL returns average values with many decimal places by default. Use ROUND function to limit decimal places for cleaner output. The second parameter specifies how many decimal places to keep. This makes results more readable and suitable for reports, especially when dealing with currency or percentages.

-- Round average to 2 decimal places
SELECT 
    ROUND(AVG(price), 2) AS avg_price_rounded,
    AVG(price) AS avg_price_full
FROM products;

🔹 AVG with HAVING Clause

The HAVING clause filters groups based on aggregate function results, applied after GROUP BY calculations. Use it to find groups where the average meets certain criteria, such as departments with average salaries above a threshold. Unlike WHERE, HAVING works with aggregate functions like AVG.

-- Find departments with average salary over 70000
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

🔹 AVG with DISTINCT

Use DISTINCT with AVG to calculate the average of only unique values, excluding duplicates. This is useful when you want to find the average of distinct prices, ratings, or scores. Without DISTINCT, duplicate values are counted multiple times in the average calculation, which may skew results.

-- Calculate average of unique prices only
SELECT 
    AVG(price) AS avg_all_prices,
    AVG(DISTINCT price) AS avg_unique_prices
FROM orders;