?
Solved

Oracle SQL Query

Posted on 2013-10-31
2
Medium Priority
?
344 Views
Last Modified: 2013-10-31
Hi Experts,

I have the table with below columns
1.      ID
2.      WORK_ID
3.      VOLUME


It has a composite primary key ID and W_ID
Below is the data

ID       W_ID      VOL
1      1      10
2      2      10
3      2      10
3      3      10
1      4      15
3      5      10

Output : I want the ID and volume as out put, but calculating volume has some rules

1.      If Works is shared between ID’s, volume should be equally divided between them.

Sample out put depending on above table is

1 – 10 + 15 = 25
2 – 10/2 = 5 (Work 2 shared by both 2 and 3)
3 – 10/2 + 10 + 10 = 25

I need a query to get the above result.

 
Thanks,
Ravi
0
Comment
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39615995
try this.
select ID,SUM(VOL) VOL
  from (
select t1.ID,t1.W_ID,t1.VOL/t2.cnt VOL
  from test t1 
  join (select W_ID,COUNT(W_ID) cnt from test group by W_ID) t2
    on t1.W_ID = t2.W_ID) t3
 group by ID

Open in new window


http://sqlfiddle.com/#!4/8ceed/12
0
 

Author Closing Comment

by:Ravi Kiran Reddy Katkuri
ID: 39616059
Awsome, thanks a lot for the help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question