PostgreSQL GROUP BY

Introduction to PostgreSQL GROUP BY

The group by a statement in PostgreSQL divides the rows from the select statement into groups; we can apply an aggregate function to each group in PostgreSQL group by clause, group by clause is very important in PostgreSQL to divide rows from the select statement into no of groups. It is also used to collaborate the select statement to group the output data to this group which was identical in nature; this is used to eliminate redundancy of data into the output to compute aggregates that apply to those groups.

Syntax

Select expr1, expr2, …. , exprN, (Column name)
aggregate_function (expr)
From tables
Where [ condition ] GROUP BY expr1, expr2, …. , exprN

Select column_list (list of column we have used to fetch data from table)
From table_name (table name)
Where [ condition ] GROUP BY col1, col2, …, colN (List of column that used in group by clause)
ORDER BY col1, col2, …, colN (List of column that used in order by clause)

Select expr1, expr2, …. , exprN, (Column name which is used fetch data from table)
From table_name (Name of table)
Where [ condition ] GROUP BY col1, col2, …, colN (Column list)
ORDER BY col1, col2, …, colN

Below is the parameter description of the above syntax:

• expr 1 to expr N: It is nothing but the column name that we have used in the table to fetch data from the table.
• An aggregate function: The aggregate function that we have used in group by clause to fetch data from the table can be (SUM, MIN, AVG, MAX, and COUNT).
• Table name: The table name from which we are retrieving data.
• Where condition: This is optional. This is used to select specific data.
• Group by: Group by clause used to retrieve data from the table.
• Column1 to columnN: Number of columns used to retrieve data from the table.

How PostgreSQL GROUP BY clause works?

• Group by a clause in PostgreSQL is used to group together the rows which have identical data.
• This clause is used to select the statement or retrieve identical data from the table.
• This clause will collect data across multiple records and group results with one or more columns.
• This clause is also used to reduce the redundancy of data.
• Group by clause is used to reduce the redundancy of data into no rows in PostgreSQL.
• This clause will groups records into summary rows and then return large no of data into smaller sets.
• This clause divides the rows into smaller groups that have the same values in the specific column.
• This clause is used in the select statements to combine a group of rows based on rows’ values to a particular group or expression.

Examples

We have using the employee table to describe the group by clause in PostgreSQL.

Code:

CREATE TABLE Employee ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, PRIMARY KEY (emp_name));

Output:

Code:

# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (1, 'ABC', 'Pune', '1234567890', 20000);
# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (1, 'PQR', 'Pune', '1234567890', 20000);
# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (1, 'XYZ', 'Mumbai', '1234567890', 35000);
# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (2, 'BBS', 'Mumbai', '1234567890', 45000);
# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (2, 'RBS', 'Delhi', '1234567890', 50000);

Output:

Example #1 – Using SUM function

Code:

testing=# SELECT emp_id, sum(emp_salary) AS "Salary of employee" FROM employee GROUP BY emp_id;

Output:

Example #2 – Using MIN function

Code:

SELECT emp_id, MIN(emp_salary) AS "Salary of employee" FROM employee GROUP BY emp_id;

Output:

Example #3 – Using MAX function

Code:

SELECT emp_id, MAX(emp_salary) AS "Salary of employee" FROM employee GROUP BY emp_id;

Output:

Example #4 – Using the AVG function

Code:

SELECT emp_id, AVG(emp_salary) AS "Salary of employee" FROM employee GROUP BY emp_id;

Output:

Example #5 – Using COUNT function

Code:

SELECT emp_id, count(*) AS "No of employee" FROM employee GROUP BY emp_id;

Popular Course in this category

PostgreSQL Course (2 Courses, 1 Project)
  2 Online Courses |  1 Hands-on Project|  7+ Hours|  Verifiable Certificate of Completion
4.5

Course Price

View Course

---

Output:

Importance

• This clause is used to merge the number of columns in one set.
• This clause is used to collaborate the rows with select statements for identical data.
• Identical data will merge in the clause. Group by clause is most important in PostgreSQL.
• The group by clause in PostgreSQL follows after where clause in the select statement and after the order by clause.
• This clause is most important in PostgreSQL.

Conclusion

Group by clause is most important in PostgreSQL to retrieve data from a single set. Group by clause is used in a select statement to collect data from multiple rows, and this result is grouped into one or more columns. Identical data will merge in this clause.

This has been a guide to PostgreSQL GROUP BY. Here we discuss the introduction, how it works with different examples. You may also have a look at the following articles to learn more –

1. What is PostgreSQL?
2. PostgreSQL Triggers
3. A Quick Glance of MySQL Trigger
4. PostgreSQL Views