# Calculate percentage of false records to total records within a aggregate function

I'm looking for a solution to calculate a percentage rate of a boolean value:

Model:

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?

Markus

-
you mean "how many values are true?" 1 is true – gbn Jan 10 '12 at 15:21

Note the standard GROUP BY clause.

This gives "true" percent because I assume that's what you mean (not false)

``````SELECT
p.*,
count(CASE WHEN c.essential = 1 THEN 1 END) / count(*) AS essential_percentage
From
products p
LEFT OUTER JOIN
components c ON p.id = c.product_id
GROUP BY
p.*;
``````

or

``````  count(nullif(c.essential, 0)) / count(*) AS essential_percentage
``````
-
cool thats it! thank you very much! – Markus Jan 10 '12 at 15:35