PostgreSQL HAVING

Key HAVING Concepts

SELECT city, COUNT(*)
FROM customers
GROUP BY city
HAVING COUNT(*) > 5;

SELECT category, AVG(price)
FROM products
GROUP BY category
HAVING AVG(price) > 100;

-- WHERE: before grouping
-- HAVING: after grouping
SELECT city, COUNT(*)
FROM customers
WHERE active = true
GROUP BY city
HAVING COUNT(*) > 3;

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 
   AND COUNT(*) > 5;

🔹 Basic HAVING Example

Find cities with more than 2 customers. HAVING filters the grouped results, showing only cities that meet the count condition after GROUP BY has organized the data.

-- Sample table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

-- Insert sample data
INSERT INTO customers (name, city) VALUES
('John', 'Boston'),
('Jane', 'Boston'),
('Mike', 'Seattle'),
('Sarah', 'Boston'),
('Tom', 'Seattle');

-- HAVING query
SELECT 
    city,
    COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 2;

🔹 HAVING with SUM and AVG

Filter customers based on their total spending or average order value. HAVING lets you identify high-value customers by applying conditions to aggregated sales data.

-- Find customers with total purchases over $500
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent,
    AVG(amount) AS avg_order
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500
ORDER BY total_spent DESC;

🔹 WHERE vs HAVING

WHERE filters individual rows before grouping, while HAVING filters groups after aggregation. You can use both together: WHERE narrows down rows first, then HAVING filters the grouped results.

-- Using both WHERE and HAVING
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE in_stock = true          -- Filter rows BEFORE grouping
GROUP BY category
HAVING AVG(price) > 50         -- Filter groups AFTER grouping
ORDER BY avg_price DESC;

🔹 Multiple HAVING Conditions

Combine multiple conditions in HAVING clause using AND or OR operators. This allows complex filtering of grouped data based on multiple aggregate criteria simultaneously.

-- Find departments with specific criteria
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5 
   AND AVG(salary) > 60000
   AND MAX(salary) < 150000
ORDER BY avg_salary DESC;

🔹 HAVING with MIN and MAX

Use HAVING with MIN and MAX functions to filter groups based on minimum or maximum values. This helps identify groups with specific range characteristics in your data.

-- Find product categories with price range criteria
SELECT 
    category,
    COUNT(*) AS product_count,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products
GROUP BY category
HAVING MIN(price) > 20 
   AND MAX(price) < 1000;