Group By in PostgreSQL

In this PostgreSQL tutorial, I will show you how to use GROUP BY in PostgreSQL with syntax. Also, you will understand how to use the GROUP BY clause with aggregate functions such as count(), sum() and etc.

Additionally, I will show you how to use the GROUP BY clause with multiple columns and the JOIN clause.

Group By in PostgreSQL

The GROUP BY clause is used to divide the rows into groups with the same values, the result returned by the SELECT statement can be broken down into several groups based on the columns which are specified in the GROUP BY clause.

The syntax is given below.

SELECT column_names
FROM table_name
WHERE [ conditions ]
GROUP BY column_name1, column_name2....column_nameN
ORDER BY column_name1, column_name2....column_nameN

Always use the GROUP BY clause after FROM and WHERE clause.

• GROUP BY column_name1, column_name2, ….column_nameN: You a specify any number of columns with the GROUP BY clause and make user these column’s names should also exist in the SELECT part of the query.
• ORDER BY column_name1, column_name2…column_nameN: The order by clause order the rows in a specific order such as ascending and descending order.
  

Let’s understand with a simple example. Suppose you have a table called ‘orders’ which contains the information about the total item ordered by the customer on a specific date and the first, and last name of the customer with customer Id.

Now you want to group the order of the total items by the customers in table ‘orders’.

SELECT first_name, total_items
    FROM orders
GROUP BY  total_items, first_name
ORDER BY total_items;

The above query returns the result which contains the total item ordered by the customer. But returned result contains rows grouped by the column total_items and first_name (GROUP BY total_items, first_name) as you can see in the output the same group contains the same value.

Let’s see with an example how you can use the GROUP BY in PostgreSQL to group the rows.

Group By in PostgreSQL with Aggregate() function

Suppose you need to perform the calculation group-wise, you can use the aggregated () function within the query containing the GROUP BY clause.

Let’s say want to find the total item order by each customer, use the below query.

SELECT first_name, sum(total_items)
    FROM orders
GROUP BY first_name;

The SELECT part of the above query returns the first name of the customer with the total items ordered by each customer. Then using the GROUP BY clause with column first_name, grouping the result of rows according to the first name of the customer.

Let’s use the COUNT() function with the GROUP clause. Suppose you want to count the number of orders placed by each customer, you can use the below query.

SELECT first_name, count(total_items)
    FROM orders
GROUP BY first_name;

The SELECT part of the above query returns the first name of the customer and how many times each customer placed the order. Then using the GROUP BY clause with column first_name, grouping the result of rows according to the first name of the customer.

You can also use the other aggregate function() within the query containing group by clause such as min(), max(), average() and etc.

Group By in PostgreSQL with More Than One Column

In PostgreSQL, you can group the rows based on the multiple columns, or as I said in the above intro part while explaining the syntax you can use any number of columns with the GROUP BY clause but the specified columns in the GROUP BY must appear in the SELECT part of the query.

Suppose, you have to group the orders by order_date and then total_items, then you can specify both the columns order_date, and total_items with GROUP BY clause, So use the below query.

SELECT order_date, total_items
    FROM orders
GROUP BY order_date, total_items
ORDER BY order_date;

The above query returns the order of the total items on a specific date and the result set is ordered by order_date and total_items as you can see in the above output.

Group By in PostgreSQL with JOIN clause

The GROUP BY clause can also be used with the JOIN clause to retrieve information from more than one table.

Suppose you have two table orders and order_details as shown in the below picture.

Now you want to retrieve the total quantity order by each customer and grouping by the column customer id, so you can use the below query.

SELECT 
    o.customer_id,
    SUM(od.quantity) AS total_quantity
FROM 
    orders o
JOIN 
    order_details od ON o.order_id = od.order_id
GROUP BY 
    o.customer_id;

The above query returns the total quantity ordered by each customer as you can see in the above output the customer with id 3 has ordered a total quantity of 3 and with id 4 has ordered a total quantity of 2 and so on.

Conclusion

In this PostgreSQL, you learned how to use the GROUP BY clause to divide the rows into groups. Then you learned how to use the GROUP BY clause with function count(), sum(), and also how to use the GROUP BY clause with JOIN clause and multiple columns.

You may also read:

• How to Use Postgresql Having Clause
• Postgresql Sum – How to use
• PostgreSQL Order By with Examples