Calculate min sum

, ,
1

I have some event ,that have several records , each record has value that represents Mass (m) .

I need to find the "minimum sum of the squares of the mass " from all the possible combinations from each pairs of Mass for this particular event .

I can create 2 partitions with all the possible combinations and then check the pair with the minimum sum.

for example - we have 3 values (can be much more ) , the partition will look like this -

P1 = v1+v2 P2 = v3
P1 = v1+v3 P2 = v2
P1 = v2+v3 P2 = v1

and then we check which pair has the minimum sum of squares of mass value .

Any ideas how to built those partition in SQL ? or how to solve the min sum problem in a different way ?

4
create table dbo.events(eventname varchar(50)) create table dbo.eventsmass(eventmassid int not null, eventname varchar(50), mass int ) create table dbo.partitions(eventid1 int , eventid2 int, partitionvalue int) insert into dbo.events select 'big bang' union select 'big bang' union select 'big bang' union select 'big bang' union select 'big bang' insert into dbo.eventsmass select 1, 'big bang', 100 union select 2, 'big bang', 10000 union select 3, 'big bang', 55 union select 4, 'big bang', 33 union select 5, 'big bang', 12 insert into dbo.partitions select a.eventmassid, b.eventmassid, (SQUARE(a.mass) + SQUARE(b.mass)) partitionvalue from eventsmass a cross apply eventsmass b where a.eventname = b.eventname and a.eventmassid <> b.eventmassid select top 1 partitionvalue top1, * from partitions order by partitionvalue asc select partitionvalue sortdesc, * from partitions order by partitionvalue asc drop table dbo.events drop table dbo.eventsmass drop table dbo.partitions
5

Hi ,

Thank you so much for the reply , but what if your table looks like this :

insert into dbo.eventsmass
select 1, 'big bang', 100
union
select 2, 'big bang', 10000
union
select 3, 'big bang', 55
union
select 4, 'big bang', 33
union
select 5, 'big bang', 12
union
select 1, 'big bang2', 25
union
select 2, 'big bang2', 15
union
select 3, 'big bang2', 30
union
select 1, 'big bang3', 45
union
select 2, 'big bang3', 75
union
select 3, 'big bang3', 30
union
select 4, 'big bang3', 60

Is your query will work ? i think we need more than 1 cross join , how can you tell how many cross joins to do ?

6

yes that will work. you will need only one cross join, that is the beauty of cross join

7

Yeah you right ,

The thing is that it returning me the same partition value twice -

big bang 2 1 100010000

big bang 1 2 100010000

i think this 2 rows represent the same thing , how can i avoid them ?

8

Another thing , i think you miss understood my problem .

The solution that you mention is giving me the sum of 2 values only , but i need the sum of all permutation within every possible combination for each event name .

example -
select 1, 'big bang', 10
union
select 2, 'big bang', 20
union
select 3, 'big bang', 30
union
select 4, 'big bang', 40

Here i have 4 mass values , so i have 7 combinations ( (2^(n-1)) -1 ) - the "n" is 4 .

the square sum combinations are -

10^2 | 20^2 + 30^2 + 40^2
10^2 + 20^2 | 30^2 + 40^2
10^2 + 20^2 +30^2 | 40^2
10^2 + 30^2 | 20^2+40^2
10^2 + 30^2 + 40^2 | 20^2
20^2+30^2 | 10^+40^2
20^2+30^2+40^2 | 10^2

How can i get this in a query ??

9

so for this latest data sample you have given, how is it that you have 7 combinations? Can you please explain?

10

yes , 7 combination of square sum -

I have 7 combination for splitting the sum values to 2 groups , i have 4 different values for "big bang" event - 10,20,30,40 , so i have ( (2^(n-1)) -1 ) - which is 7 possibilities of splitting this 4 numbers to 2 groups ,
If i have 10 in Group1 so the other values (20,30,40) should be in Group2 .

the groups are splitted by the " | " .

Group1                        Group2
  1. 10^2 | 20^2 + 30^2 + 40^2
  2. 10^2 + 20^2 | 30^2 + 40^2
  3. 10^2 + 20^2 +30^2 | 40^2
  4. 10^2 + 30^2 | 20^2+40^2
  5. 10^2 + 30^2 + 40^2 | 20^2
  6. 20^2+30^2 | 10^+40^2
  7. 20^2+30^2+40^2 | 10^2
11

so

  1. 10^2 | 20^2 + 30^2 + 40^2 and 7) 20^2+30^2+40^2 | 10^2 are not the same? to the average pedestrian like me, they seem the same, just transposed.

ok, so another question. what does this symbol | represent? can you represent this in layman terms?

12

my mistake , 7) should be -

10^2+20^2+40^2 | 30^2

the symbol | is just delimiter between the 2 groups :slight_smile: