SQL Subquery not working in Redshift



  • 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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2