Divide the output of two queries

Question

I would like to write a query that outputs a / c (a divided by c) Grouped By the variable called cobrand_id.

What is the most seamless way to write this query? Thank you.

    select sum(calc) a, cobrand_id b 
from temp_08.jwn_calc 
where optimized_transaction_date > '2015-10-31' 
and optimized_transaction_date <= '2015-10-31' + 45 
GROUP BY cobrand_id

    select sum(calc) c, cobrand_id d 
from temp_08.jwn_calc 
where optimized_transaction_date > '2015-10-31' 
and optimized_transaction_date <= '2015-10-31' + 91 
GROUP BY cobrand_id

score 2 · Answer 1 · answered Dec 17 '16 at 20:17

2

You can use your existing queries as subqueries and join them to divide a/c

Select q1.b, q1.a/q2.c as result
From (your first query) q1
Inner join (your second query) q2 on q1.b = q2.d

answered Dec 17 '16 at 20:17

under

2,519
1
21
40

score 1 · Accepted Answer · edited Dec 18 '16 at 21:25

1

Use conditional aggregation:

select cobrand_id,
      Sum(case when optimized_transaction_date > '2015-10-31' 
and optimized_transaction_date <= '2015-10-31' + 45 then Calc end) /
      Sum(case when optimized_transaction_date > '2015-10-31' 
and optimized_transaction_date <= '2015-10-31' + 91 then Calc end)
from temp_08.jwn_calc 
where optimized_transaction_date > '2015-10-31' 
and optimized_transaction_date <= '2015-10-31' + 91 
GROUP BY cobrand_id

edited Dec 18 '16 at 21:25

under

2,519
1
21
40

answered Dec 17 '16 at 22:31

Gordon Linoff

1,242,037
58
646
786

Thank you. I would greatly appreciate if you can help solve the more challenging http://stackoverflow.com/questions/41215444/creating-a-function-in-sql-to-iterate-through-dates, which leverages your work above to create a function. – ZJAY Dec 19 '16 at 03:42

Divide the output of two queries

2 Answers2