Show min value of duplicate row value as column value

Question

More detailed question. I have a table called CALCS with the structure: code is the record identifier (not unique) value1, value2, value3,value4 are different values to sum price is the cost of work I must return price+value1+value2+value3+value4 for all records. In case there are duplicate records (2 times the same code, not more times), i need a new column called value5 or compute value, with the minus value of value 1, and must appears in the sum. Example of records:

code    value1  value2  value3  value4  price
001579  93,1    0       0      0       280
001585  83,13   0       0      0       250
001592  250     0       0      0       500
001592  50      0       0      0       500
001593  84      0       0      33      240

The only record duplicate is code 001592. The result i need will be as:

code    value1  value2  value3  value4  price    total
001579  93,1    0       0      0       280       373,1
001585  83,13   0       0      0       250       333,13
001592  250     0       0      0       500       800 (first row values + value 1 of second row)
001593  84      0       0      33      240       357

Thanks,

Answer 1

If there are always two rows per CODE, you can consider VALUE1 to be the maximum value and VALUE2 to be the minimum:

SELECT CODE, MAX(VALUE1) AS VALUE1, MIN(VALUE1) AS VALUE2
  FROM mytable
 GROUP BY CODE;

Of course, this returns a single row even if there are more rows per CODE, but it's not clear from your question what exactly you're trying to achieve.

Answer 2

Rather you can create a view in which derive the desired value with a window function through grouping by code such as

CREATE OR REPLACE VIEW v_tab AS 
SELECT t.*, MIN(value1) OVER (PARTITION BY code) AS value2
  FROM tab t --yourTable

Answer 3

Using a cte to get minval per code

drop table if exists t;
create table t
(CODE int,        VALUE1 int);
insert into t values
(2345   ,     250),
(2345   ,     50);

with cte as 
(select code,min(value1) as value2 from t group by code)
select t.*,cte.value2
from t
join cte on cte.code = t.code and cte.value2 <> value1;

+------+--------+--------+
| CODE | VALUE1 | value2 |
+------+--------+--------+
| 2345 |    250 |     50 |
+------+--------+--------+
1 row in set (0.001 sec)