0

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,

oraculo
  • 13
  • 4
  • 1
    Are there only ever 2 values for code? – P.Salmon Sep 26 '22 at 14:36
  • How did you calculate the total of 800? Did you add the sum of 250 and 50 to the price of the first row? – Mihe Sep 27 '22 at 09:52
  • The total of 800 is the sum of value1 (250) + value2 (0) + value3 (0)+value4(0)+price(500)+value1(50) of the second row with the same code (001592) – oraculo Sep 27 '22 at 10:05
  • There is no way of identifying first and second rows based on the published sample data so what you are asking is not possible. Also this is so different from the original question that for your sake you should have deleted and raised new given that viewers may simply pass over if a number of answers have been posted. – P.Salmon Sep 27 '22 at 10:15
  • I note in a comment to my answer that you actually use mariadb rather than mysql - the 2 dialects are not the same and you should tag mariadb. – P.Salmon Sep 27 '22 at 10:18
  • What happen to `01592 - value1 = 50`? It doesn't show in your expected result. Also, will other value than `value1` have similar value format like `93,1`? – FanoFN Sep 28 '22 at 04:34

3 Answers3

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
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Doesn't this give 2 rows in output? – P.Salmon Sep 26 '22 at 14:45
  • Yes, it gives two rows @P.Salmon , but not clear whether OP wants to have like this or not, considering the logic of adding a virtual column against the current appearance. – Barbaros Özhan Sep 26 '22 at 14:46
  • Hi @BarbarosÖzhan, i've tried this solution but in view, all values in value1 and value2 are the same – oraculo Sep 26 '22 at 18:14
  • first of all, do you have some more sample data to reproduce the case, and Can you please tell what's the version of your DB @oraculo ? And what will exactly be your desired output for the given sample data set you'll present ? Can you edit the question with such details ... – Barbaros Özhan Sep 26 '22 at 18:17
  • Btw, not **all** but some(only minimum) values should return identical @oraculo – Barbaros Özhan Sep 26 '22 at 18:31
  • Hi @BarbarosÖzhan, i've edited question. I hope it seems clearly now. – oraculo Sep 27 '22 at 08:12
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)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • Hi P.Salmon, i've tried this but gives me an error with sintaxis – oraculo Sep 26 '22 at 18:15
  • If you are on a version prior to 8 convert cte to sub query - and publish the version you are on. – P.Salmon Sep 27 '22 at 05:41
  • Hi P.Salmon, i have version 10.2.36-MariaDB – oraculo Sep 27 '22 at 07:26
  • Cte's were introduced in mariadb 10.2.1 so this should syntax for you. what error message did you get. – P.Salmon Sep 27 '22 at 10:21
  • Hi P.Salmon, it works fine, was my mistake with one column. Ok, this returns value2 as virtual column only for duplicate rows. Is possible, take a list like the last edit i made on question, where i can see non repeat rows and, in case there was repetaed rows, see only a line with the new column? Thnka, a lot, – oraculo Sep 27 '22 at 11:11
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.

Mihe
  • 2,270
  • 2
  • 4
  • 14
  • Hi @Mihe, not always are two rows per code, and there are more columns in table. What i need is that when two rows wih the same code, can see a third "virtual" column with the value of the second row for the value column. I tried your query but always returns 0 – oraculo Sep 26 '22 at 18:05
  • I don't know, what you've done, but the query can't return 0. Here's a [fiddle](https://dbfiddle.uk/Yku2qnsr) where you can try it on your own. But as you want the value of the second row, it doesn't help you anyway and you've to go with one of the other solutions. – Mihe Sep 26 '22 at 18:40
  • Hi @Mihe, sorry, it works, not return 0. At this point, it's valid. Not all records have two rows, CODE does not repeat always. I have a TOTAL field where i calculate the sum of VALUE1+OTHERCOLUMN and must have also VALUE2 where available. The OTHERCOLUMN (total) is a VIRTUAL GENERATED field. I can´t put like expression the select. How can i resolve this? – oraculo Sep 27 '22 at 07:57
  • Heres an updated [fiddle](https://dbfiddle.uk/dtuGBHzh) – Mihe Sep 27 '22 at 16:04
  • Hi Mihe, it seems works perfectly. Only one thing, in the example, is ok, it shows code, value1, value2, value3,value4, price and total. Is there a way for display the value of the repeated row? For example, in the fiddle, for code 2345 are two values for value1 column; 250 and 50. In the result, it display the value1 of the first row. Can i show a new column in this final row called value_extra with the 50? – oraculo Sep 28 '22 at 09:27
  • Sure, you just need to select m2.value2, too. Here's an updated [fiddle](https://dbfiddle.uk/-FIgyhMD). Perhaps you want to add a CASE that results in 0, if m.value1 equals m.value2, but that's up to you. – Mihe Sep 28 '22 at 09:34
  • Hi, thanks again. I'm new in this, sorry. This is ok, but i don´t understand how show this value only for repeated, in the fiddle, code 2345 is correct, shows in the extra_value column the value of repeated row, but in code 3310, it must 0 or empty, because there's no repetaed row. Thanks a lot, – oraculo Sep 28 '22 at 09:47
  • Hi @Mihe, "Sure, you just need to select m2.value2, too. Here's an updated fiddle. Perhaps you want to add a CASE" works OK. But there's a new column, YEAR, then records os one year can have the same code in other year – oraculo Mar 01 '23 at 10:08