Open navigation menu

HAVING for Group Filtering

Uses HAVING clause to filter grouped results based on aggregate conditions, combined with WHERE for pre-aggregation row filtering.

SqlData Fetching

Code

snippet
SELECT
    c.category_id,
    c.category_name,
    COUNT(p.product_id) AS product_count,
    AVG(p.unit_price) AS avg_price,
    SUM(p.stock_quantity * p.unit_price) AS inventory_value,
    MIN(p.unit_price) AS min_price,
    MAX(p.unit_price) AS max_price,
    STDDEV(p.unit_price) AS price_stddev
FROM categories c
JOIN products p ON c.category_id = p.category_id
WHERE p.discontinued_date IS NULL
  AND p.stock_quantity >= 0
GROUP BY c.category_id, c.category_name
HAVING COUNT(p.product_id) >= 5
   AND AVG(p.unit_price) > 20.00
   AND SUM(p.stock_quantity * p.unit_price) > 1000.00
   AND MAX(p.unit_price) / MIN(p.unit_price) < 10
ORDER BY inventory_value DESC;

-- HAVING with window function comparison
SELECT
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
)
   AND COUNT(*) > 3
ORDER BY avg_salary DESC;

Related Examples