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.

 

ORDER BY Clause
    SELECT expressions  
    FROM tables  
    [WHERE conditions]  
    ORDER BY expression [ ASC | DESC ];

 

Parameters:

ParametersDescription
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:

Example

postgresql Order by & Group by

 

The following example will sort the result in ascending order by firstname:

 

ORDER BY with ASC
     SELECT * FROM payroll  
     WHERE lastname = 'Yakkiparamban'  
     ORDER BY firstname ASC;

 

Output

postgresql Order by & Group by

 

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.

Syntax:
     SELECT expression1, expression2, ... expression_n,   
     aggregate_function (expression)  
     FROM tables  
     [WHERE conditions]  
     GROUP BY expression1, expression2, ... expression_n;

 

Parameters:

ParametersDescription
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.

Query
     SELECT lastname FROM payroll ;

The output will be:

 

Output

postgresql Order by & Group by

 

To get the unique values for lastnames, we can use the following query.

Query
      SELECT lastname FROM payroll 
      GROUP BY lastname;

 

Output

postgresql Order by & Group by

 

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.

Query
      SELECT lastname, COUNT(*) FROM  payroll   
      GROUP BY lastname;

 

Output

postgresql Order by & Group by

Muhsin Yakkiparamban

Muhsin Yakkiparamban is the co-founder of Lauyou Learning. Muhsin holds Bachelor Degree in Civil Engineering from KMCT Calicut.

You may also like...