aggregate on join latest value This is a problem I often get stuck in and I hope there is a solution.

Let's build an example:

Table products: product_id, product_name
Table Prices: product_id, valid_since, Price


Lets say I would like to know the sum of all prices of all products (seems senseless, but it is just for illustrating the problem in an easy way) 

Now you can imagine, prices will change with time, so the only price that matters is the price with the highest value of Prices.valid_since of course

SELECT SUM(Prices.price) 
FROM products 
INNER JOIN Prices
ON products.product_id=Prices.product_id


will result in something absolutely senseless, so how could it be possible?
As the way your schema current sits, you'll need two parts; the main select statement and (since you are using MySQL, which doesn't support common table expressions), a view in the where clause.  

The view, priveView:

SELECT product_id
, MAX (valid_since) [validSince]
FROM prices
GROUP BY product_id


The select statement:

SELECT SUM (P.Prices)
FROM Prices AS P
INNER JOIN priceView as PV ON P.product_id = PV.Product_Id AND PV.valid_since = P.valid_since 


If possible I would change the schema of your prices tables so that it also includes a column of when the price starts in additional when it ends/changes.  Whatever the current price happens to be has it's "price_end" value set to null.  You'd then be able to avoid the need for an intermediate view that gets the sub-section of rows that apply.