How to PostgreSQL- Order By & Group By
In this guide, we are going to learn how to use PostgreSQL ORDER BY & GROUP BY statements with Select. Before you continue its recommended to read previous PostgreSQL guides.
PostgreSQL ORDER BY Clause
The PostgreSQL ORDER BY keyword is used to sort the result-set by one or more columns.
It sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ ASC | DESC ];
|expressions:||The columns that you want to retrieve.|
|tables:||It specifies the tables, from where you want to retrieve records. There must be at least one table listed in the FROM clause.|
|WHERE conditions:||It is optional. It is the conditions that must be fulfilled for the records to be selected.|
|ASC:||It is optional. It sorts the result set in ascending order by expression|
|DESC:||It is also optional. It sorts the result set in descending order by expression.|
Consider the table payroll having records as follows:
The following example will sort the result in ascending order by firstname:
SELECT * FROM payroll WHERE lastname = 'Yakkiparamban' ORDER BY firstname ASC;
PostgreSQL GROUP BY Clause
The PostgreSQL GROUP BY clause is a SQL command that is used to group rows that have the same values. The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item.
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
|expression1, expression2, ... expression_n:||It specifies the expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.|
|aggregate_function||It specifies a function such as SUM, COUNT, MIN, MAX, or AVG etc. tables: It specifies the tables, from where you want to retrieve the records. There must be at least one table listed in the FROM clause.|
|WHERE conditions||It is optional. It specifies the conditions that must be fulfilled for the records to be selected.|
Grouping using a Single Column Example:
Let’s execute a simple query that returns all the lastname entries from the contacts table.
SELECT lastname FROM payroll ;
The output will be:
To get the unique values for lastnames, we can use the following query.
SELECT lastname FROM payroll GROUP BY lastname;
GROUP BY with an aggregate function:
Suppose we want the total number of contacts those have the same lastname. We can use the following query to do that.
SELECT lastname, COUNT(*) FROM payroll GROUP BY lastname;