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!

try using this:

select 
DISTINCT productID, 
regionID 
min(price) as MINP 
from sales_db 
where available_volume>5 
group by productID, regionID 
order by min(price) desc
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.