What if you use left join instead of inner join? Hm, the same result probably.
Also, investigate the data in your database, does every transaction having ProdID xyz, also have an object in association table Transaction_Product? You can check this by comparing the Mendix-id’s of both objects: transaction/id and product/id.
If that still does not clear up the issue, add a snapshot of three records in Transaction, the related records in transaction_Product, and the one record in Product to this forum-question. That way we can see the data you are trying to sum.
On https://mydemoversion8-sandbox.mxapps.io/p/OQL, your query is similar to the button “Count, Sum and Group by enumeration”
select c.CustomerType, count(*) Ordercount, sum(o.TotalPrice) TotalSpent from "Sales.Customer" c join "c/Sales.Order_Customer/Sales.Order" o group by c.CustomerType;
Just replace their ‘Customer’ with your ‘Product’ and their ‘Order’ with your ‘Transaction’