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:

  1. Select the rows with the same parameters (a,b) i.e.group column redundant values
  2. After grouping the table with respect to a,b the output column c must be summed .
  3. After grouping the table with respect to a,b the output column t1 must be selected with minimal value.
  4. 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

Need Your Help

Automating Word to insert an image from a URL

automation ms-word javascript

from Automation in jscript how can I programatically insert an image into Word (2000+) from a URL?