PostgreSQL Group By Clause

Syntax:

SELECT select_list FROM ... [WHERE ...] GROUP BY grouping_column_reference [, grouping_column_reference]...

The GROUP BY Clause is used to group together those rows in a table that have the same values in all the columns listed. The order in which the columns are listed does not matter. The effect is to combine each set of rows having common values into one group row that represents all rows in the group. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups.

Create and insert data into the table.

CREATE table test(x varchar(10),y int); CREATE TABLE k2info=# insert into test values('a',3); INSERT 0 1 k2info=# insert into test values('c',2); INSERT 0 1 k2info=# insert into test values('b',5); INSERT 0 1 k2info=# insert into test values('a',2); INSERT 0 1 k2info=# insert into test values('b',3); INSERT 0 1 k2info=# insert into test values('d',3); INSERT 0 1

Examples:

1. Get the rows of above table group by x values.

select x from test group by x;

2. Get all the rows from table test group by x.

select * from test group by x;
ERROR: column “test.y” must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from test group by x;

In the above query, we could not have written SELECT * FROM test1 GROUP BY x, because there is no single value for the column y that could be associated with each group. The grouped by columns can be referenced in the select list since they have a single value in each group.

Powered by k2schools