CODEWITHSUNDEEP
sqloracle-databasesubqueryoracle-apex
honeymoon
honeymoon

Reputation: 2520

SQL subqueries calculations

I have this table:

╔═════╤═══════╤═══════╗
║ ID  │ Group │ Value ║
╠═════╪═══════╪═══════╣
║ ID1 │ 1     │ 10    ║
╟─────┼───────┼───────╢
║ ID2 │ 1     │ 5     ║
╟─────┼───────┼───────╢
║ ID2 │ 1     │ 4     ║
╟─────┼───────┼───────╢
║ ID2 │ 1     │ 6     ║
╟─────┼───────┼───────╢
║ ID1 │ 2     │ 1     ║
╟─────┼───────┼───────╢
║ ID2 │ 2     │ 7     ║
╟─────┼───────┼───────╢
║ ID2 │ 2     │ 8     ║
╟─────┼───────┼───────╢
║ ID2 │ 2     │ 3     ║
╟─────┼───────┼───────╢
║ ID3 │ 3     │ 6     ║
╟─────┼───────┼───────╢
║ ID2 │ 3     │ 4     ║
╟─────┼───────┼───────╢
║ ID2 │ 3     │ 1     ║
╟─────┼───────┼───────╢
║ ID2 │ 3     │ 9     ║
╚═════╧═══════╧═══════╝

I want to do the following:

  1. Find all Groups where ID = ID1
  2. Extract the average values of ID2 belonging to the same group as ID = ID1

ID1 is only found in Group1 and Group2. Therefore the average of ID2 in Group1 = (5+4+6)/3 = 5

and Group2 = (7+8+3)/3 = 6

  1. Calculate the percent of the value of ID1 relative to the average of ID2 for each group.

Group1 = (10*100%)/5 = 200%

Group2 = (1*100%)/6 = 16%

I know how to do the steps separately but I don't know how I can use the list of the first queries (the groups) in the second query.

Search_Item = 'ID1'

Select Group FROM MyTable WHERE ID = 'ID1'

-> Return 1 and 2

Select AVG(Value) FROM MyTable WHERE ID = 'ID2' AND Group = '1' 
Select AVG(Value) FROM MyTable WHERE ID = 'ID2' AND Group = '2'

Any help would be very appreciated.

Upvotes: 1

Views: 1176

Answers (2)

sagi
sagi

Reputation: 40481

You can do something like this:

SELECT t.group,NVL(avg(s.value),0),(MAX(t.value)*100/NVL(avg(s.value),1)) as Perc
FROM(
    SELECT group,max(value) as value FROM MyTable WHERE ID = 'ID1' group by group) t
LEFT OUTER JOIN Mytable s
 ON(t.group = s.group and s.ID = 'ID2')
GROUP BY t.group

Upvotes: 2

MT0
MT0

Reputation: 167972

You can do it using a single table scan like this:

SELECT   "GROUP",
         100 * SUM( CASE id WHEN 'ID1' THEN value END )
             / AVG( CASE id WHEN 'ID2' THEN value END )
           AS Percentage
FROM     MyTable
GROUP BY "GROUP"
HAVING   COUNT( CASE id WHEN 'ID1' THEN 1 END ) > 0;

Output

GROUP PERCENTAGE
----- ----------
    1        200
    2 16.6666667

If you only want groups where there is exactly one row where the ID is ID1 then change the last line to:

HAVING   COUNT( CASE id WHEN 'ID1' THEN 1 END ) = 1;

It is possible that you could have a group with an ID1 row and the sum of the ID2 rows is zero (if you allow zero or negative values) - the query would then throw an ORA-01476: divisor is equal to zero exception. If you want to prevent this from occurring then you can use:

SELECT   "GROUP",
         100 * SUM( CASE id WHEN 'ID1' THEN value END )
             / CASE AVG( CASE id WHEN 'ID2' THEN value END )
                    WHEN 0 THEN NULL
                    ELSE AVG( CASE id WHEN 'ID2' THEN value END )
                    END
           AS Percentage
...

Upvotes: 1

Related Questions