I would like to calculated an expected profit.
I have got the following table:
OrderID
productID
regionID
available_volume
price
sales_per_day
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:
productID
regionID
expected_sales
profit
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!