PostgreSQL

In PostgreSQL, the GROUP BY clause is used in a SELECT query to arrange identical data into groups. It is commonly used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to perform operations on a group of rows that share a particular column's value. This allows you to group rows based on one or more columns and perform operations on each group separately. For example, you can calculate the total balance of customers grouped by the type of account they hold in a banking database.

How to Use GROUP BY in PostgreSQL

1. Basic Structure of GROUP BY: The GROUP BY clause is used after the WHERE clause and before the ORDER BY clause (if any).

   SELECT column_name, aggregate_function(column_name)
   FROM table_name
   WHERE condition
   GROUP BY column_name;
   

2. Example of Grouping Data:Let's say you have a table called transactions with the following columns:
   • transaction_id: The unique ID of each transaction
   • customer_id: The ID of the customer
   • amount: The amount of the transaction
   • transaction_date: The date when the transaction occurred

   You can group the transactions by the customer_id and calculate the total transaction amount for each customer.

   SELECT customer_id, SUM(amount) AS total_spent
   FROM transactions
   GROUP BY customer_id;
   

3. Grouping Multiple Columns: You can group by more than one column. For instance, to group transactions by both customer_id and the transaction_date, you can do the following:

   SELECT customer_id, transaction_date, SUM(amount) AS total_spent
   FROM transactions
   GROUP BY customer_id, transaction_date;
   

4. Using HAVING with GROUP BY: The HAVING clause is used to filter groups based on a condition after the GROUP BY has been applied. It is similar to the WHERE clause, but works on aggregated data.

   Example: Get the customers who have spent more than $500 in total.

   SELECT customer_id, SUM(amount) AS total_spent
   FROM transactions
   GROUP BY customer_id
   HAVING SUM(amount) > 500;
   

5. Combining GROUP BY with Other Aggregates: In a GROUP BY query, you can use multiple aggregate functions to get various statistics. For example, getting the count of transactions and the total amount spent by each customer:

   SELECT customer_id, COUNT(transaction_id) AS transaction_count, SUM(amount) AS total_spent
   FROM transactions
   GROUP BY customer_id;
   

This should give beginners a good start with understanding how GROUP BY works in PostgreSQL along with examples relevant to a banking scenario.

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

TEST CODE

In PostgreSQL, to calculate the total count of clients in each state, you can use the following query:

SELECT state, COUNT(client_id)
FROM org_client
GROUP BY state;

Try it now

In PostgreSQL, to determine the aggregate salary disbursement by each department, you can use the following query:

SELECT department, SUM(salary)
FROM org_employee
GROUP BY department;

Try it now

In PostgreSQL, to identify the year of birth of the youngest client from each state, you can use the following query:

SELECT state, MAX(birth_year)
FROM org_client
GROUP BY state;

Try it now

In PostgreSQL, to calculate the number of orders for each day, you can use the following query:

SELECT DATE(order_date) AS order_date,
COUNT(order_number) AS order_count
FROM act_order
GROUP BY DATE(order_date);

Try it now

In PostgreSQL, to tally the orders handled by each employee or sales agent, 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;

Try it now

In PostgreSQL, to get the number of female clients in each city, you can use the following query:

SELECT city, COUNT(client_id) AS client_count
FROM org_client
WHERE gender = 'F'
GROUP BY city;

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