Home » SQL & PL/SQL » SQL & PL/SQL » Random distribution of sum (Oracle 11g)
|
|
|
Re: Random distribution of sum [message #649712 is a reply to message #649706] |
Mon, 04 April 2016 09:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
|
Re: Random distribution of sum [message #649718 is a reply to message #649717] |
Mon, 04 April 2016 11:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8938 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.
|
|
|
Goto Forum:
Current Time: Sun Jun 30 15:37:31 CDT 2024
|