x
Solved

# Oracle SQL select within select  with calculation

Posted on 2014-04-03
Medium Priority
481 Views
I need help coding some SQL. THERE is one table called B_TABLE.

B_TABLE can have MANY Bills for 1 PROV

Each PROV can have many CL,
Each CL FOR A PROVIDER CAN HAVE  1 to many CA

Want to show EACH unique PROV where  and their CA / CL ratio

PROV       CL           CA      AMT
A                aa           123       10.00
A                aa           234         5.00
A                aa           234       20.00
A               aa            345       15.00
A               dd           456       25.00
A               ee           717       25.00

B               bb           877     100.00
B               cc             342    200.00

The query results for the example above should be

A   1.67    70.00
B   1        300.00

I am having trouble coding the SQL.
0
Question by:ktylerconk
• 2

LVL 32

Expert Comment

ID: 39976032
Can you explain how those results were determined and what does the 1.67 represent and if the 300.00 for B represents a sum, why is A 70.00? I think a little more detail of what you want to accomplish would help.
0

Author Comment

ID: 39976140
Sum of A should be 100.00. Sorry, I added rows.

Ratio of 1.66 is the CA divided by CL.   a flag will be generated if the ratio us greater than 2.
So if A had 6 CA's but only 2 CL's, there would be a flag raised because the ratio would be 3.
0

LVL 36

Accepted Solution

johnsone earned 800 total points
ID: 39976451
Try this:

``````select prov, count(distinct ca) / count(distinct cl), sum(amt)
from b_table
group by prov;
``````

It gives the result:

``````PROV	COUNT(DISTINCTCA)/COUNT(DISTINCTCL)	SUM(AMT)
A       1.666666666667                          100
B       1                                       300
``````
0

Author Closing Comment

ID: 39976556
Worked like a charm! Thank you!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.