OraFAQ Forum: SQL & PL/SQL » Random distribution of sum

Home » SQL & PL/SQL » SQL & PL/SQL » Random distribution of sum (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Random distribution of sum [message #649706]

Mon, 04 April 2016 07:02

NikoTrend1
Messages: 4
Registered: April 2016

Junior Member

Hi, my friends!

I need help in an interesting topic (Oogh, that's my headache:))
For example, i have such structure as:

CREATE TABLE t (
Column1 varchar2(7),
Column2 number,
Column3 numder,
Column4 number
)

Example:
Column1; Column2; Column3; Column4
2010.01; 1; 10; 1
2010.01; 1; 20; 1
2010.01; 1; 30; 2
2010.01; 2; 10; 7
2010.01; 2; 20; 2
2010.01; 2; 30; 3
2010.02; 1; 10; 1
2010.02; 1; 20; 2
2010.02; 1; 30; 5
2010.02; 2; 10; 1
2010.02; 2; 20; 1
2010.03; 1; 10; 1
2010.03; 3; 10; 2

I know that:

SELECT SUM (Column4) FROM t
GROUP BY Column1,Column2

should be:
2010.01;1 = 3 (but now 4)
2010.01;2 = 13 (but now 12)
2010.02;1 = 9 (but now 8 )
2010.02;2 = 3 (but now 2)
2010.03;1 = 1 (like now)
2010.03;3 = 2 (like now)

I want to built distribution which will be contain int numbers in Column4,
but sum in groups shold be mine instead Column4.

For example:
Column1; Column2; Column3; Column4
2010.01; 1; 10; 1
2010.01; 1; 20; 1
2010.01; 1; 30; 1
2010.01; 2; 10; 7
2010.01; 2; 20; 3
2010.01; 2; 30; 3
2010.02; 1; 10; 2
2010.02; 1; 20; 2
2010.02; 1; 30; 5
2010.02; 2; 10; 2
2010.02; 2; 20; 1
2010.03; 1; 10; 1
2010.03; 3; 10; 2

I hope you rescue me Smile

Report message to a moderator

Re: Random distribution of sum [message #649707 is a reply to message #649706]

Mon, 04 April 2016 07:34

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Can you explain explain your logic again? I do not understand this at all,
Quote:

I want to built distribution which will be contain int numbers in Column4,
but sum in groups shold be mine instead Column4.

Report message to a moderator

Re: Random distribution of sum [message #649709 is a reply to message #649706]

Mon, 04 April 2016 07:50

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Report message to a moderator

Re: Random distribution of sum [message #649712 is a reply to message #649706]

Mon, 04 April 2016 09:24

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Are you sure you need:

2010.01;1 = 3 (but now 4)

and not

2010.01;1 = 5 (but now 4)

Assuming you want 5, not 3:

with t as (
           select '2010.01' column1,1 column2,10 column3,1 column4 from dual union all
           select '2010.01',1,20,1 from dual union all
           select '2010.01',1,30,2 from dual union all
           select '2010.01',2,10,7 from dual union all
           select '2010.01',2,20,2 from dual union all
           select '2010.01',2,30,3 from dual union all
           select '2010.02',1,10,1 from dual union all
           select '2010.02',1,20,2 from dual union all
           select '2010.02',1,30,5 from dual union all
           select '2010.02',2,10,1 from dual union all
           select '2010.02',2,20,1 from dual union all
           select '2010.03',1,10,1 from dual union all
           select '2010.03',3,10,2 from dual
          )
select  column1,
        column2,
        sum(column4) now,
        sum(column4) + sign(count(*) - 1) desired
  from  t
  group by column1,
           column2
  order by column1,
           column2
/

COLUMN1    COLUMN2        NOW    DESIRED
------- ---------- ---------- ----------
2010.01          1          4          5
2010.01          2         12         13
2010.02          1          8          9
2010.02          2          2          3
2010.03          1          1          1
2010.03          3          2          2

6 rows selected.

SQL>

SY.

Report message to a moderator

Re: Random distribution of sum [message #649716 is a reply to message #649712]

Mon, 04 April 2016 11:45

NikoTrend1
Messages: 4
Registered: April 2016

Junior Member

Thanks.
Yes, I sure, it's just for example.
I need a distribution of any quantity (it may be bigger or smaller than sum in groups).

Report message to a moderator

Re: Random distribution of sum [message #649717 is a reply to message #649707]

Mon, 04 April 2016 11:49

NikoTrend1
Messages: 4
Registered: April 2016

Junior Member

John, thanks!
I have any quantity and i want to distribute this to some lines

Report message to a moderator

Re: Random distribution of sum [message #649718 is a reply to message #649717]

Mon, 04 April 2016 11:59

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Sorry, man, but unless you explain the rule for the transformation I do not see how anyone can assist. It is no good just showing two row sets, you have to say how they are related.

Report message to a moderator

Previous Topic:	subquery with group by issue
Next Topic:	find Characters which are not present in keyboard

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:46:53 CDT 2024