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
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.)