MYSQL select data from two tables with conditions-CodePudding

So I basically got a task which is to create a single query to retrieve the total order as Number_Of_Order and total sales of all orders as Total_Sales_Amount. My main problem would be in creating the conditions for Normal and Promotion.

Expected result :

Number_Of_Order | Total_Sales_Amount

Total number of Order_ID from Order table | total sales from Orders_Products table according to Sales_type price.

CodePudding user response：

Use a join along with a CASE expression to use the correct price.

SELECT COUNT(DISTINCT o.Order_ID) AS Number_Of_Order,
       SUM(CASE WHEN o.Sales_Type = 'Normal'
                THEN op.Normal_Price ELSE op.Promotion_Price END) AS Total_Sales_Amount
FROM Orders o
LEFT JOIN Orders_Products op
    ON op.Order_ID = o.Order_ID;