The PostgreSQL GROUP BY clause is used to divide rows returned by SELECT statement into different groups. The speciality of GROUP BY clause is that one can use Functions like
SUM() to calculate the sum of items or
COUNT() to get the total number of items in the groups.
Syntax: SELECT column_1, column_2, computing_function(column_3) FROM table_name GROUP BY column_1, column_2;
It is important to note that The GROUP BY clause must exactly appear after the FROM or WHERE clause.
For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.
Here we will query for data from the payment table and group the result by customer id from the “payment” table of our sample database.
SELECT customer_id FROM payment GROUP BY customer_id;
Here we will query to get the amount that each customer has paid till date and use an aggregate function (ie SUM()), to do so and group them by customer_id from the “payment” table of the sample database.
SELECT customer_id, SUM (amount) FROM payment GROUP BY customer_id;
here we will make a query to count the number of payment transactions that each staff has been processing, you group the rows in the payment table based on staff_id and use the COUNT() function to get the number of transactions.
SELECT staff_id, COUNT (payment_id) FROM payment GROUP BY staff_id;
- PostgreSQL - HAVING clause
- PostgreSQL - WHERE clause
- PostgreSQL - LIMIT clause
- PostgreSQL - FETCH clause
- PostgreSQL - ORDER BY clause
- PostgreSQL - LIMIT with OFFSET clause
- PostgreSQL - SELECT DISTINCT clause
- PostgreSQL - CTE
- PostgreSQL - Primary Key
- PostgreSQL - IN operator
- PostgreSQL - Joins
- PostgreSQL - INNER JOIN
- PostgreSQL - SELF JOIN
- PostgreSQL - Size of value
- PostgreSQL - COALESCE
- PostgreSQL - UPDATE
- PostgreSQL - SELECT
- PostgreSQL - NOT IN operator
- PostgreSQL - Subquery
- PostgreSQL - Alias
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.