Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
you mean "how many values are true?" 1 is true –  gbn Jan 10 '12 at 15:21
add comment

1 Answer

up vote 3 down vote accepted

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
share|improve this answer
    
cool thats it! thank you very much! –  Markus Jan 10 '12 at 15:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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