SQL Subquery not working in Redshift

Posted on

Question :

I’m trying to calculate report for ratio per week. Ratio = Sales_Dollars/Inventory_Dollars
Sales_dollars per week and Inventory_dollars per week are calculated separately in 2 different queries (A, B) as shown below.
Now i need to join them to get ratio per week. However when I try to do so, I get a syntax error in AWS redshift DB

(select Extract (Week from date) as "Week", Sum(A.unit_retail_price_usd * B.quantity_sold) as "Sales_Dollars" from d_products A
Join f_sales B On A.product_id = B.product_id
Join d_date D On B.date_id = D.date_id
group by Week order by Week) as A

(select Extract (Week from date) as "Week", Sum(A.unit_cost_usd * C.quantity_purchased) as "Inventory_Dollars" from d_products A
Join f_inventory C On A.product_id = C.product_id 
Join d_date D On C.date_id = D.date_id
group by  Week order by Week) as B

Select Week, (A.Sales_Dollars/B.Inventory_Dollars) As Ratio from A
Join B on A.Week = B.Week

Expected Result:

Week Ratio
1 1.5
2 4.5

enter image description here

Answer :

You tagged it with MySQL, so I will answer it for that.

SELECT  `week`, 
        a.sd / b.id AS "Ratio"
    FROM ( SELECT `week`, SUM(...) AS sd FROM .. JOIN .. ) AS a
    JOIN ( SELECT `week`, SUM(...) AS id FROM .. JOIN .. ) AS b  USING(`week`)

(I don’t know about Redshift.)

Leave a Reply

Your email address will not be published. Required fields are marked *