CODEWITHSUNDEEP
sqlpostgresql
ZJAY
ZJAY

Reputation: 2807

Divide the output of two queries

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

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 1

under
under

Reputation: 3067

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

Upvotes: 2

Related Questions