PostgreSQL presents the GROUP BY clause to permit the customers to kind teams of rows when working with the SELEC assertion. You possibly can kind teams of rows from single or a number of columns.
The GROUP BY clause can be utilized with or with out completely different mixture features. This submit guides you in understanding find out how to work with the PostgreSQL GROUP BY clause. We are going to give quite a few examples to make sure that you’re comfy working with it. Let’s start.
PostgreSQL GROUP BY Syntax
The GROUP BY clause is used with the SELECT assertion to kind teams. Its syntax is as follows:
SELECT column1, column2, aggregate_function(column_n) FROM table_name GROUP BY column1, column2;
Within the syntax, you begin by writing the “choose” assertion. Subsequent, it’s essential to specify the column(s) from which you want to kind the teams of rows. If utilizing an mixture operate reminiscent of COUNT(), MAX(), SUM(), AVG(), and many others., it’s essential to specify it and the column that you simply goal to use the combination operate.
You could then specify the desk that you’re utilizing, and the GROUP BY assertion follows with the chosen column(s).
Let’s have examples.
Instance 1: GROUP BY with out the Mixture Perform
The mixture operate is non-obligatory. For this case, we use the next desk for our examples:
Right here’s a easy GROUP BY question that makes use of a single column to group the standing that’s used within the desk. From the output, we see that it returns two rows:
The command selects the “standing” column from our desk and teams the chosen column into rows. On this case, we solely have the energetic or inactive choices; the 2 rows are returned.
Instance 2: GROUP BY with SUM()
For this instance, we return a sum of the “emp_id” column. Nonetheless, we choose the “standing” column for the grouping. By operating the command within the following picture, we will see that it sums the emp_id values for every entry to get the entire energetic and inactive rows:
Be aware that the brand new row that’s fashioned makes use of a reputation which is related to the combination operate. Nevertheless, you’ll be able to specify what identify to make use of. As an illustration, the next instance specifies the identify utilizing the AS assertion:
Instance 3: GROUP BY with COUNT()
Not like SUM(), the COUNT() returns the variety of situations relying on the chosen column. For this instance, we use COUNT() to return a row which comprises the variety of occasions that the standing column is represented.
The chosen column is “standing”. Thus, the output returns the variety of occasions that every occasion of the worth within the “standing” column seems.
You possibly can apply this instance whenever you wish to rely the situations {that a} given worth seems in a specific column in your desk.
Instance 4: GROUP BY with A number of Columns
To date, we’ve solely seen find out how to choose a single column when working with the GROUP BY clause. Nevertheless, you’ll be able to choose a number of columns by separating them with commas. The chosen columns should even be specified after the GROUP BY assertion.
On this instance, we choose the “identify” and “wage” columns:
Instance 5: GROUP BY Clause Mixed with Different Clauses
PostgreSQL helps completely different clauses. When working with the GROUP BY clause, combining it with different clauses reminiscent of ORDER BY is feasible.
This instance selects the completely different columns and specifies the SUM(). Furthermore, we order the association in descending order utilizing the ORDER BY clause. The output reveals the listing of names on our desk and their standing that are organized in descending order primarily based on the wage column:
With that, you’ve seen find out how to use the GROUP BY clause in your PostgreSQL database for various actions. Combining the GROUP BY clause with different clauses creates particular queries.
Conclusion
When working with the SELECT assertion, the GROUP BY clause is right whenever you wish to group the rows in your desk. This tutorial presents the completely different examples of working with the GROUP BY clause. Observe the offered examples and use them to get comfy with the PostgreSQL GROUP BY clause.
