SQL doing math find smallest value and then insert into column

Question

i have 2 table this is my first table

+-----------+---------+------+------+---------+---------+-------------+
|   name    |  squad  | val1 | val2 | result1 | result2 | finalresult |
+-----------+---------+------+------+---------+---------+-------------+
| Andrew    | alpha   |    1 |    2 |         |         |             |
| Brian     | bravo   |    3 |    4 |         |         |             |
| Chris     | charlie |    5 |    6 |         |         |             |
| Danny     | alpha   |    7 |    8 |         |         |             |
| Elise     | bravo   |    9 |   10 |         |         |             |
| Fransisca | charlie |   11 |   12 |         |         |             |
+-----------+---------+------+------+---------+---------+-------------+

and this is my second table

+---------+----------+------+------+
|  squad  | subsquad | val3 | val4 |
+---------+----------+------+------+
| alpha   | air      |    1 |    2 |
| alpha   | land     |    3 |    4 |
| alpha   | sea      |    5 |    6 |
| bravo   | air      |    1 |   -2 |
| bravo   | land     |    3 |   -4 |
| bravo   | sea      |    5 |   -6 |
| charlie | air      |   -1 |    2 |
| charlie | land     |   -3 |    4 |
| charlie | sea      |   -5 |    6 |
+---------+----------+------+------+

i want to insert data into result1, result2, and finalresult

finalresult = MIN ((val1+val3) * (val2+val4)for every subsquad) 
result1 = val 3 that have smallest value in finalresult
result2 = val 4 that have smallest value in finalresult

here's example table that i would like to achieve

+-----------+---------+------+------+---------+---------+-------------+
|   name    |  squad  | val1 | val2 | result1 | result2 | finalresult |
+-----------+---------+------+------+---------+---------+-------------+
| Andrew    | alpha   |    1 |    2 |       1 |       2 |           8 |
| Brian     | bravo   |    3 |    4 |       8 |      -2 |         -16 |
| Chris     | charlie |    5 |    6 |       0 |      12 |           0 |
| Danny     | alpha   |    7 |    8 |       8 |      10 |          80 |
| Elise     | bravo   |    9 |   10 |      14 |       4 |          56 |
| Fransisca | charlie |   11 |   12 |       6 |      18 |         108 |
+-----------+---------+------+------+---------+---------+-------------+

and here's what i've done

SELECT name, a.squad, val1, val2, result1, result2, ((val1+val3)*(val2+val4)) as finalresult, b.squad, subsquad, val3, val4
FROM a
LEFT JOIN b ON a.squad=b.squad

It's unlikely (and, if true, unfortunate) that you have a column called group. But, that aside, please see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Aug 10 '20 at 03:44
i changed my column from group into squad thanks for the information — Reza, Aug 10 '20 at 03:56
this could help you for SQLfiddle Checkout this https://stackoverflow.com/q/7745609/808921 — Mohtisham Zubair, Aug 10 '20 at 07:58

score 3 · Accepted Answer · answered Aug 10 '20 at 12:43

3

Checkout this http://sqlfiddle.com/#!9/e995b/1

select ad.* from (
SELECT name, a.squad, val1, val2, (val1+val3) as result1, (val2+val4) as result2, ((val1+val3)*(val2+val4)) as finalresult
FROM a
CROSS JOIN b ON a.squad=b.squad
) as ad
inner join ( 
SELECT name, min((val1+val3)*(val2+val4)) as finalresult
FROM a 
LEFT JOIN b ON a.squad=b.squad
group by name,a.squad
 ) as f
 on ad.name = f.name and ad.finalresult = f.finalresult

answered Aug 10 '20 at 12:43

Mohtisham Zubair

723
5
15

thank's i need a few adjustment i need to post result1,result2, and final result into table a also i'm gonna run this every a few minute so it would be better if do some checking result1 and result2 first if it is null – Reza Aug 19 '20 at 05:14

劉鎮瑲 · Answer 2 · 2020-08-10T14:04:05.597

Do finalresult = MIN ((val1+val3) * (val2+val4)for every subsquad) for every row on a.squad= b.squad like you have done and rank each group of name/squad order by finalresult.

  select a.name,a.squad,a.val1,a.val2,b.val3 as result1,b.val4 as result2,
  (a.val1+b.val3) * (a.val2+b.val4) as finalresult,
  row_number() over (partition by a.name,a.squad order by (a.val1+b.val3) * (a.val2+b.val4)) 
  from a join b on a.squad=b.squad

Then you just select above table with condition rn=1.

  select name,squad,val1,val2,result1,result2,finalresult from
  (
    select a.name,a.squad,a.val1,a.val2,b.val3 as result1,b.val4 as result2,
      (a.val1+b.val3) * (a.val2+b.val4) as finalresult,
      row_number() over (partition by a.name,a.squad order by (a.val1+b.val3) * (a.val2+b.val4)) 
      from a join b on a.squad=b.squad
  ) where rn=1

In case of the DB version is lower 8.

Just using @Mohtisham Zubair 's anwser.

@BarbarosÖzhan My bad.I didn't consider the DB version. – 劉鎮瑲 Aug 10 '20 at 14:00 — 劉鎮瑲, Aug 10 '20 at 14:00

score 0 · Answer 3 · answered Aug 10 '20 at 12:01

0

If you want to update the original table, then the logic would look like:

update table1 t1 join
       (select t1.*, t2.val3, t2.val4,
               (t1.val1 + t2.val3) * (t1.val2 + t2.val4) as finalresult,
               row_number() over (partition by t1.name, t1.squad order by (t1.val1 + t2.val3) * (t1.val2 + t2.val4)) as seqnum
        from table1 t1 join
             table2 t2
             using (squad)
       ) tt
       on tt.name = t1.name and tt.squad = t1.squad and
          tt.val1 = t1.val1 and tt.val2 = t1.val2
    set t1.result1 = tt.val3,
        t1.result2 = tt.val4,
        t1.finalresult = tt.finalresult
    where tt.seqnum = 1;

answered Aug 10 '20 at 12:01

Gordon Linoff

1,242,037
58
646
786

provided that the DB version is 8 – Barbaros Özhan Aug 10 '20 at 12:02
@BarbarosÖzhan . . . There is no reason to think that the OP is *not* using version 8. It has been out for over two years. – Gordon Linoff Aug 10 '20 at 12:02
still we need to ask him or restrict the scope within the answer I think – Barbaros Özhan Aug 10 '20 at 12:04
my DB version is 5.5.56 – Reza Aug 19 '20 at 01:05
how do i update my original table using @Mohtisham Zubair 's anwser. – Reza Aug 19 '20 at 05:15
@Reza . . . Shouldn't that question be directed to Mohtisham Zubair? – Gordon Linoff Aug 19 '20 at 14:34

SQL doing math find smallest value and then insert into column

3 Answers3

Linked