# PostgreSQL Aggregation (Grouping ,SUM ,MIN,MAX) competent exercise

I have an competent exercise to aggregate tables in a certain way (not a familiar one) where I tried to use the Grouping , sum , max, min functions but I couldn't find a logic behind to combine all : giving an example as below :

db=# select * from test; id | t1 | t2 | a | b | c ----+----+----+---+---+---- 1 | 1 | 2 | x | y | 2 2 | 1 | 2 | y | x | 4 3 | 2 | 3 | x | y | 6 4 | 2 | 4 | y | x | 8 5 | 3 | 4 | x | x | 10 6 | 4 | 5 | x | x | 12 7 | 4 | 7 | x | x | 14

the exercise is to create table test2 with the following rules:

- Select the rows with the same parameters (a,b) i.e.group column redundant values
- After grouping the table with respect to a,b the output column c must be summed .
- After grouping the table with respect to a,b the output column t1 must be selected with minimal value.
- After grouping the table with respect to a,b the output column t2 must be selected with maximum value.

The output test2 will be selected from test as below

db=# select * from test2; id | t1 | t2 | a | b | c ----+----+----+---+---+---- 1 | 1 | 3 | x | y | 6 2 | 1 | 4 | y | x | 12 5 | 3 | 7 | x | x | 36

for reference the table is created as below:

create table test (id BIGSERIAL PRIMARY KEY,t1 int4,t2 int4, a text, b text ,c INT64_UNSIGNED); insert into test values (1,1,2,'x','y',2); insert into test values (2,1,2,'y','x',4); insert into test values (3,2,3,'x','y',6); insert into test values (4,2,4,'y','x',8); insert into test values (5,3,4,'x','x',10); insert into test values (6,4,5,'x','x',12); insert into test values (7,4,7,'x','x',140;

To create the table in SQL server -

CREATE TABLE [dbo].[test]( [id] [int] NULL, [t1] [int] NULL, [t2] [int] NULL, [a] [varchar](50) NULL, [b] [varchar](50) NULL, [c] [int] NULL ) ON [PRIMARY]

## Answers

This is Your Query

Solution for competent exercise

select MIN(id) as id, MIN(t1) as t1, Max(t2) as t2, a, b, sum(c) as c from dbo.test group by a,b order by id