SQL problem

UDA
Enthusiast

SQL problem

Dear Friends,

I am facing a problem writing SQL for a scenario

Scenario:- I have two table a & b as listed below.

user_id total PS Percent
1 32 1 25
2 24 2 75
3 12
4 13
5 54
6 23
7 25
8 23
9 12
10 41

in the table a i have user_id and the respective totals and in the table b i have the percent split i have to make.

I need to split the 10 accounts into 25:75 and send it 1 & 2 accordingly. I am out of ideas about this problem.

any inputs?
4 REPLIES
Enthusiast

Re: SQL problem

user_id total
1 32
2 24
3 12
4 13
5 54
6 23
7 25
8 23
9 12
10 41
259

PS Percent
1 25
2 75

Re: SQL problem

What do you mean by "send it to 1 & 2"?
Enthusiast

Re: SQL problem

user_id total
1 32
2 24
3 12
4 13
5 54
6 23
7 25
8 23
9 12
10 41
259

PS Percent
1 25
2 75

what i mean is the totals records in the first table needs to be split in 25% and 75% between 1 & 2 in the second table based on total.

let us say in our example i can send following records to PS 1

10 41
9 12
3 12

and the remaining records to ps2. I know this is more like a statistical problem rather than a technical one.
rgs
Enthusiast

Re: SQL problem

I think you are not getting a response because you are not very clear on what you want to do. But if you are trying to split the input rows into two groups at random on a 25%, 75% split then the following will do that. Not sure that is what you want.

select * from u1 order by 1;

*** Query completed. 10 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

user_id total
----------- -----------
1 32
2 24
3 12
4 13
5 54
6 23
7 25
8 23
9 12
10 41

select x.a, sum(x.total) from (select case when random(1,100) >25 then 2 else 1 end as a, total from u1) as x group by x.a;

*** Query completed. 2 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

a Sum(total)
---- -----------
1 93
2 166