In PostgreSQL GROUP BY Statement and
SELECT
Statement to group the same data.
GROUP BY is in a The basic syntax of the GROUP BY clause is given below: The GROUP BY clause must be placed in the In the GROUP BY clause, you can group one or more columns, but the grouped columns must exist in the list. Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows: The following example will be based on The following results are obtained: Now let’s add the following statement in the Now Now on the basis of The results are as follows: The following example uses the ORDER BY clause with the GROUP BY clause: The following results are obtained:
SELECT
Statement, put it in the
WHRER
After the clause and in front of the ORDER BY clause. 5.24.1. Grammar ¶
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
WHERE
After the condition in the clause, it must be placed before the ORDER BY clause. 5.24.2. Example ¶
runoobdb# 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)
NAME
Field values are grouped to find out each person’s total salary:runoobdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
name | sum
-------+-------
Teddy | 20000
Paul | 20000
Mark | 65000
David | 85000
Allen | 15000
Kim | 45000
James | 10000
(7 rows)
CAMPANY
Add three records to the table: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);
COMPANY
There are duplicate names in the table, and the data is as follows: 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)
NAME
Field values are grouped to find out the total payroll of each customer:runoobdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
name | sum
-------+-------
Allen | 15000
David | 85000
James | 20000
Kim | 45000
Mark | 65000
Paul | 40000
Teddy | 20000
(7 rows)
runoobdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
name | sum
-------+-------
Teddy | 20000
Paul | 40000
Mark | 65000
Kim | 45000
James | 20000
David | 85000
Allen | 15000
(7 rows)