I'm looking for a solution to calculate a percentage rate of a boolean value:
Products 1 <-> n Components
Components.essential is a boolean attribute
What I want to calculate is something like:
SELECT products.*, count(comp_a.essential)/count(comp_b.essential) AS essential_percentage From products INNER JOIN components AS comp_a ON products.id = comp_a.product_id INNER JOIN components AS comp_b ON products.id = comp_b.product_id WHERE comp_a.essential = 1 GROUP BY products.id
Unfortunately this statement calculates way too many components as there are two INNER JOIN on the components model...
Any help on this?