Question 113486 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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,

Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search