coverage percentage question solved through a complex sql query...?

Ok, i've been trying to solve this for about 2 hours now... Please advise:

Tables:

PROFILE [id (int), name (varchar), ...]
SKILL   [id (int), id_profile (int), id_app (int), lvl (int), ...]
APP     [id (int), ...]

The lvl can basically go from 0 to 3.

I'm trying to get this particular stat: "What is the percentage of apps that is covered by at least two people having a skill of 2 or higher?"

Thanks a lot

From stackoverflow

• SELECT SUM( CASE lvl WHEN 3 THEN 1 WHEN 2 THEN 1 ELSE 0 END ) / SUM(1) FROM SKILL
  

  If your database has an if/then function instead of CASE, use that. For example, in MySQL:

  SELECT SUM( IF( lvl >= 2, 1, 0 ) ) / SUM(1) FROM SKILL
  

  Rory : This counts skills instead of apps.

• Untested

  select convert(float,count(*)) / (select count(*) from app) as percentage
  from (
      select count(*) as number
      from skill
      where lvl >= 2
      group by id_app ) t
  where t.number >= 2
  

• I'm not sure if this is any better or worse than tvanfosson's answer, but here it is anyway:

  SELECT convert(float, count(*)) / (Select COUNT(id) FROM APP) AS percentage
  FROM APP INNER JOIN SKILL ON APP.id = SKILL.id 
  WHERE (
     SELECT COUNT(id) 
     FROM SKILL AS Skill2 WHERE Skill2.id_app = APP.id and lvl >= 2
  ) >= 2
  

  Rory : your inner join on skill will mean you count apps more than once. You only want to count each qualifying app once.

• The logic is: percentage = 100 * ( number of apps of interest ) / ( total number of apps )

  select 'percentage' = 
  -- 100 times
    ( cast( 100 as float ) * 
  -- number of apps of interest
    ( select count(id_app) 
      from ( select id_app, count(*) as skilled_count
             from skill
             where lvl >= 2
             group by id_app 
             having count(*) >= 2 ) app_counts ) 
  -- divided by total number of apps
    / ( select count(*) from app )
  

  The convert to float is needed so sql doesn't just do integer arithmetic.

• SELECT AVG(covered)
  FROM (
    SELECT CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS covered
    FROM app a
    LEFT JOIN skill s ON (s.id_app = a.id AND s.lvl >= 2)
    GROUP BY a.id
  )
  

  More efficient way for MySQL:

  SELECT AVG
         (
           IFNULL
           (
             (
             SELECT 1
             FROM skill s
             WHERE s.id_app = a.id
             AND s.lvl >= 2
             LIMIT 1, 1
             ), 0
           )
         )
  FROM app a
  

  This will stop counting as soon as it finds the second skilled person for each app.

  Efficient if you have a few app's but lots of person's.

  Rory : that's nice! I never think to use average.

  m_oLogin : very nice thanks!!