3

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
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.