The PostgreSQL GROUP BY provision is utilized in a joint effort with the SELECT assertion to amass those lines in a table that have indistinguishable information. This is done to dispose of excess in the yield as well as register totals that apply to these gatherings.
The GROUP BY proviso follows the WHERE condition in a SELECT proclamation and goes before the ORDER BY statement.
Syntax
The essential linguistic structure of GROUP BY statement is given underneath. The GROUP BY proviso should follow the conditions in the WHERE statement and should go before the ORDER BY statement on the off chance that one is utilized.
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
You can utilize more than one segment in the GROUP BY condition. Ensure whatever section you are utilizing to gathering, that segment ought to be accessible in segment list.
Example
Consider the table COMPANY having records as follows −
# select * from COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
In the event that you need to know the aggregate sum of compensation of every client, at that point GROUP BY inquiry would be as per the following −
testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
This would deliver the accompanying outcome −
name | sum
-------+-------
Teddy | 20000
Paul | 20000
Mark | 65000
David | 85000
Allen | 15000
Kim | 45000
James | 10000
(7 rows)
Presently, let us make three additional records in COMPANY table utilizing the accompanying INSERT explanations −
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
Presently, our table has the accompanying records with copy names −
id | name | age | address | salary
----+-------+-----+--------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
8 | Paul | 24 | Houston | 20000
9 | James | 44 | Norway | 5000
10 | James | 45 | Texas | 5000
(10 rows)
Once more, let us utilize similar proclamation to bunch by all the records utilizing NAME segment as follows −
testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
This would create the accompanying outcome −
name | sum
-------+-------
Allen | 15000
David | 85000
James | 20000
Kim | 45000
Mark | 65000
Paul | 40000
Teddy | 20000
(7 rows)
Allow us to utilize ORDER BY condition alongside GROUP BY proviso as follows −
testdb=# SELECT NAME, SUM(SALARY)
FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
This would create the accompanying outcome −
name | sum
-------+-------
Teddy | 20000
Paul | 40000
Mark | 65000
Kim | 45000
James | 20000
David | 85000
Allen | 15000
(7 rows)