I would like to calculated an expected profit.
I have got the following table:
What I would like to have:
A table grouped by productID and regionID each with the (expected) profit per product and region. Profit is calculated by price*expected_sales with following conditions:
The price is the minimal price per region where available_volume > 5
select productID, regionID min(price) as MINP from sales_db where available_volume>5 group by productID, regionID order by min(price) desc
The expected_sales are the lower quartile, practically the TOP 25% of the sales_per_day:
set @b=1; select @b:=ceil(count(orderID)/4) from sales_db where `productID` = 1236 and regionID=30000142; PREPARE STMT FROM 'select `sales_per_day`as expected_sales from sales_db where `productID` = 1236 and regionID=30000142 order by sales_per_day desc LIMIT ?,1'; EXECUTE STMT USING @b;
As you can see I have to type the productID and regionID manually and will not get a full result over the complete database.
How can I get a table that looks like this:
grouped by productID, regionID and sort by profit desc
Since 2 days I am trying - am quite desperate now :-(
Thanks for your time to help me out!