Search code examples
mysqlmariadbvirtual-column

Show min value of duplicate row value as column value

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,

Solution

  • 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.