-2

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

2
  • 1
    What you show doesn't look like a valid SQL statement (or are they three separate statements). You should include the exact query you run and the exact error message you get (no screenshots please).
    – mustaccio
    Jan 12 at 17:33
  • This is the exact query I'm trying to run. but it gives me syntax error. Please see attached screenshot
    – maayya007
    Jan 12 at 17:50
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.