Question Locked

oracle: how to "easily" divide two resultsets?

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I have the following two SQLs and their resultsets (table names have been changed):

PRODUCTS:
select sum(q1), sum(q2), sum(q3), sum(q4)
from product_history
where technology_type = 'Stocked'
and year = :year;

Q1 Q2 Q3 Q4
---------- ---------- ---------- ----------
40 60 35 0

STAFF:
select sum(q1_fte) q1, sum(q2_fte) q2, sum(q3_fte) q3, sum(q4_fte) q4
from staff_history
where stock_designator = 'Stocked'
and year = :year;

Q1 Q2 Q3 Q4
---------- ---------- ---------- ----------
40 41 40 0

I want to calculate how much staff are spent per product per quarter. Is there an "easy" way to divide each quarter column by its corresponding column in the other resultset?

For example, can I get products.q1 / staff.q1 through products.q4 / staff.q4 in one SQL statement? Something like:
products.resultset
/
staff.resultset

Has someone done this before? Suggestions greatly appreciated.

Thanks in advance!

5 replies

by wojciech.zawadzki

How about:
select
decode --and so on... for other lines(staff.q1,0,null,products.q1/staff.q1),
products.q2/staff.q2,
products.q3/staff.q3,
products.q4/staff.q4
from products,staff

by jaap

select products.q1 / staff.q1 product_per_staff_q1
,products.q2 / staff.q2 product_per_staff_q2
,products.q3 / staff.q3 product_per_staff_q3
,products.q4 / staff.q4 product_per_staff_q4
from (select sum(q1) q1
,sum(q2) q2
,sum(q3) q3
,sum(q4) q4
from product_history
where technology_type = 'Stocked'
and year = :year) products
,(select sum(q1_fte) q1
,sum(q2_fte) q2
,sum(q3_fte) q3
,sum(q4_fte) q4
from staff_history
where stock_designator = 'Stocked'
and year = :year) staff
/

by jacques.martin7

I Beleie what Jaap posted is good the only modification I would apply is making sure that if the staff.qX = 0 then the answer is 0

elect case when staff.q1 = 0 then 0 else products.q1 / staff.q1 end product_per_staff_q`
,case when staff.q2 = 0 then 0 else products.q2 / staff.q2 end product_per_staff_q2
,case when staff.q3 = 0 then 0 else products.q3 / staff.q3 end product_per_staff_q3
,case when staff.q4 = 0 then 0 else products.q4 / staff.q4 end product_per_staff_q4
from (select sum(q1) q1
,sum(q2) q2
,sum(q3) q3
,sum(q4) q4
from product_history
where technology_type = 'Stocked'
and year = :year) products
,(select sum(q1_fte) q1
,sum(q2_fte) q2
,sum(q3_fte) q3
,sum(q4_fte) q4
from staff_history
where stock_designator = 'Stocked'
and year = :year) staff
/

by apparao_v

You can use the following:
WITH
prod_sum AS
(select technology_type,year, sum(q1) q1, sum(q2) q2, sum(q3) q3, sum(q4) q4
from product_history
where technology_type = 'Stocked'
and year = :year
group by technology_type,year),
staff_sum AS
(select stock_designator,year, sum(q1_fte) q1, sum(q2_fte) q2, sum(q3_fte) q3, sum(q4_fte) q4
from staff_history
where stock_designator = 'Stocked'
and year = :year
group by stock_designator,year)
select prod_sum.q1/staff_sum.q1 "First", prod_sum.q2/staff_sum.q2 "Second", prod_sum.q3/staff_sum.q3 "Third", prod_sum.q4/staff_sum.q4 "Fourth";

This discussion is closed to new replies.