PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - HAVING clause

In PostgreSQL, the HAVING clause is used to filter the results of a GROUP BY operation. While the WHERE clause filters rows before they are grouped, the HAVING clause filters after rows have been grouped.

This means that, unlike the WHERE clause, the HAVING clause can reference aggregate functions, which produce a single result from multiple rows.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_condition;

Examples:

Suppose you have a table named sales with columns product_id, sales_date, and amount.

Using HAVING with COUNT:

Let's say you want to find products that have been sold more than 5 times.

SELECT product_id, COUNT(product_id) AS number_of_sales
FROM sales
GROUP BY product_id
HAVING COUNT(product_id) > 5;

Using HAVING with SUM:

To identify products that have sold more than a total of $1000:

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(amount) > 1000;

Combining WHERE and HAVING:
If you're interested in products that sold more than 5 times but only in the year 2022:
```
SELECT product_id, COUNT(product_id) AS number_of_sales
FROM sales
WHERE EXTRACT(YEAR FROM sales_date) = 2022
GROUP BY product_id
HAVING COUNT(product_id) > 5;
```
Here, the WHERE clause filters the rows before they are grouped, and then the HAVING clause filters the aggregated results.

Points to Note:

Always use the HAVING clause with a GROUP BY clause. Otherwise, it will not make logical sense because you won't have aggregated results to filter.
While WHERE filters rows before aggregation, HAVING filters the aggregated data.
Aggregate functions like SUM(), COUNT(), AVG(), MAX(), etc., can be used in the HAVING clause but not typically in the WHERE clause.

Using the HAVING clause effectively can help condense large datasets into more meaningful and refined results. Always ensure your queries are clear, especially when combining both WHERE and HAVING clauses, to avoid confusion.

PostgreSQL HAVING clause example:
- Description: Basic example of using HAVING to filter aggregated results.
- Code:
```
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
```
Filtering aggregated results with HAVING in PostgreSQL:
- Description: Use HAVING to filter groups based on aggregate conditions.
- Code:
```
SELECT job_title, COUNT(*)
FROM employees
GROUP BY job_title
HAVING COUNT(*) > 10;
```

HAVING vs. WHERE in PostgreSQL:

Description: Understand the difference between HAVING and WHERE in SQL.

Code:

SELECT department, AVG(salary)
FROM employees
WHERE hire_date > '2022-01-01'
GROUP BY department
HAVING AVG(salary) > 50000;

Conditional filtering with HAVING clause in PostgreSQL:
- Description: Apply conditional filters using the HAVING clause.
- Code:
```
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND AVG(salary) > 60000;
```

HAVING clause with GROUP BY in PostgreSQL:

Description: Combine HAVING with GROUP BY for more complex queries.

Code:

SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title
HAVING AVG(salary) > 55000;

Using HAVING with aggregate functions in PostgreSQL:

Description: Filter based on conditions involving aggregate functions.

Code:

SELECT department, MAX(salary) - MIN(salary) AS salary_range
FROM employees
GROUP BY department
HAVING MAX(salary) - MIN(salary) > 20000;

Multiple conditions in HAVING clause in PostgreSQL:
- Description: Apply multiple conditions using logical operators in HAVING.
- Code:
```
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 AND COUNT(*) > 5;
```

ORDER BY with HAVING in PostgreSQL:

Description: Sort the result set of a HAVING query.

Code:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY AVG(salary) DESC;

HAVING clause and window functions in PostgreSQL:

Description: Use HAVING with window functions for advanced analytics.

Code:

SELECT department, AVG(salary),
       RANK() OVER (PARTITION BY department ORDER BY AVG(salary) DESC) AS rank_within_department
FROM employees
GROUP BY department
HAVING RANK() = 1;

HAVING clause with JOIN in PostgreSQL:

Description: Combine HAVING with JOIN for more intricate conditions.

Code:

SELECT department, AVG(salary)
FROM employees
JOIN departments ON employees.department_id = departments.id
GROUP BY department
HAVING AVG(salary) > 50000;

Dynamic conditions with HAVING in PostgreSQL:

Description: Use dynamic conditions in HAVING using variables.

Code:

DO $$ 
DECLARE 
    min_salary INTEGER;
BEGIN
    min_salary := 60000;
    EXECUTE 'SELECT department, AVG(salary)
              FROM employees
              GROUP BY department
              HAVING AVG(salary) > $1' USING min_salary;
END $$;

Combining HAVING with other clauses in PostgreSQL:

Description: Integrate HAVING with WHERE, GROUP BY, and other clauses.

Code:

SELECT department, AVG(salary)
FROM employees
WHERE hire_date > '2022-01-01'
GROUP BY department
HAVING AVG(salary) > 50000;

Using HAVING for date and time conditions in PostgreSQL:
- Description: Apply HAVING for conditions involving date and time.
- Code:
```
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING EXTRACT(YEAR FROM hire_date) = 2022;
```

Nested HAVING clauses in PostgreSQL:

Description: Nest HAVING clauses for complex filtering.

Code:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
   AND COUNT(*) > 10
   AND MAX(salary) - MIN(salary) > 20000;

Using HAVING with subqueries in PostgreSQL:

Description: Employ subqueries within HAVING for advanced conditions.

Code:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees WHERE department = 'Sales');