How to create fast database queries

Archive for February 28th, 2009

Counting skills

Comments enabled. I *really* need your comment

From Stack Overflow:

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


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

We actually need to count ratio here:

(apps that have 2 or more skills with level 2+) / (total number of apps)

This is one of not so common cases when AVG aggregate apparently comes handy.
Read the rest of this entry »

Written by Quassnoi

February 28th, 2009 at 9:50 pm

Posted in MySQL