PostgreSQL

The HAVING clause in PostgreSQL is used in conjunction with the GROUP BY clause to filter groups of data based on a condition. It's similar to the WHERE clause, but while WHERE is used to filter rows before grouping, HAVING filters groups after the grouping has been done. It is particularly useful when you want to apply conditions on aggregate functions like COUNT(), SUM(), AVG(), etc.

Example Scenario: Banking System

Let's consider a scenario where we have a database for a banking system with the following tables:

• customers: Information about the bank's customers.
• accounts: Information about customer accounts.
• transactions: Information about transactions on accounts.

Example Table Structure:

-- customers table

customer_id | name       | city
------------|------------|-----------
1           | John Doe   | New York
2           | Jane Smith | Chicago

-- accounts table
account_id | customer_id | account_type
-----------|-------------|-------------
1           | 1           | Savings
2           | 2           | Checking

-- transactions table
transaction_id | account_id | amount | transaction_date
---------------|------------|--------|----------------
1              | 1          | 500    | 2024-01-01
2              | 1          | 200    | 2024-01-02
3              | 2          | 300    | 2024-01-03

Steps to Use the HAVING Clause:

1. Understand the Purpose: The HAVING clause is used to filter groups created by the GROUP BY clause. You can filter on aggregate functions like SUM(), COUNT(), etc., which is not possible with the WHERE clause.
2. Basic Example of HAVING: Let's say you want to retrieve all customers who have made transactions with a total amount greater than 400.

   SELECT customer_id, SUM(amount) AS total_amount
   FROM transactions
   GROUP BY customer_id
   HAVING SUM(amount) > 400;
   

   • Explanation:
     • SUM(amount): Calculates the total transaction amount for each customer.
     • GROUP BY customer_id: Groups the transaction data by customer_id.
     • HAVING SUM(amount) > 400: Filters out groups where the total transaction amount is less than or equal to 400.
3. Detailed Breakdown:
   1. Retrieve Data: The first step is to retrieve the required fields from the table. In this case, we retrieve the customer_id and the sum of their transaction amounts using SUM(amount).
   2. Group the Data: Use the GROUP BY clause to group the data by customer_id, so that the aggregate function (SUM()) applies to each group (customer) separately.
   3. Apply the HAVING Clause: Once the data is grouped, the HAVING clause is applied to filter out the groups based on an aggregate function. For example, only customers with a total transaction amount greater than 400 will be shown.
4. Another Example with Multiple Aggregates: If we want to find customers who have more than 1 transaction and their total transaction amount exceeds 400:

   SELECT customer_id, COUNT(transaction_id) AS total_transactions,
   SUM(amount) AS total_amount
   FROM transactions
   GROUP BY customer_id
   HAVING COUNT(transaction_id) > 1 AND SUM(amount) > 400;
   

   • Explanation:
     • COUNT(transaction_id): Counts the number of transactions for each customer.
     • HAVING COUNT(transaction_id) > 1: Filters out customers with only one transaction.
     • HAVING SUM(amount) > 400: Ensures only customers with a total transaction amount greater than 400 are selected.
5. Key Points to Remember:
   • The HAVING clause is used to filter after grouping, while the WHERE clause is used to filter before grouping.
   • You can use aggregate functions like SUM(), AVG(), COUNT(), etc., in the HAVING clause, which isn't allowed in WHERE.
   • The GROUP BY clause is mandatory when using the HAVING clause.

To gain complete access, login with gmail or outlook, no need of signup. click here

TEST CODE

In PostgreSQL, to compute the aggregate sum of salaries for each department and subsequently exhibit departments where the total salary surpasses 250,000, you can use the following query:

SELECT department,
SUM(salary) AS department_salary
FROM org_employee
GROUP BY department
HAVING SUM(salary) > 250000;

Try it now

In PostgreSQL, to display the count of orders placed by each employee, focusing on employees who have initiated more than one order, you can use the following query:

SELECT b.employee_number,
COUNT(a.order_number) AS order_count
FROM act_order a
INNER JOIN org_employee b ON b.employee_id = a.sales_agent_employee_id
GROUP BY b.employee_number
HAVING COUNT(a.order_number) > 1;

Try it now

In PostgreSQL, to retrieve data using the HAVING clause to list the order counts for married female clients with more than one order, you can use the following query:

SELECT a.client_id, a.first_name, a.last_name,
COUNT(b.order_number) AS order_count
FROM org_client a
INNER JOIN act_order b ON b.client_id = a.client_id
WHERE a.married_flag = 1
AND a.gender = 'F'
GROUP BY a.client_id, a.first_name, a.last_name
HAVING COUNT(b.order_number) > 1;

Try it now

Example 1:

In the following example, we will demonstrate the application of WHERE, GROUP BY, and HAVING clauses. It's important to note that the WHERE clause is used before GROUP BY, and the HAVING clause follows GROUP BY. Also, the HAVING clause cannot be used without preceding it with GROUP BY.

Example 1 - Raw data from client table

GROUP BY query

In this example, we will ascertain the number of married female clients in each city, and then identify cities that have more than one married female client.

SELECT city ,
count(client_id)
FROM org_client
WHERE gender= 'F'
AND married_flag = 1
GROUP BY city
HAVING count(client_id) > 1

Step 1, Apply WHERE conditions

In the image above, the cells highlighted with a green background and white font represent those that satisfy both criteria of the WHERE condition (being married females).

Step 1 output after applying WHERE condition

Step 2 - Apply GROUP BY

In the provided image, we need to group the data by the 'city' column. As observed, Albany is represented by 2 client rows, while the other cities each have only one row.

Step 2 output, after applying GROUP BY

Step 3 - Apply HAVING Clause

Using the HAVING clause essentially means filtering the grouped rows based on the condition specified in the HAVING clause. In this example, we are looking for groups with a client count greater than one. The cells highlighted in green with white font are those that meet the condition set by the HAVING clause.

Final output of entire query