Counting enums from multiple tables..

[Ragnar Brynjulfsson]

I have two tables asset and task. The asset table has id,pid,gid and name, while the task has id,name,status and aid, the last one being corresponding to the asset.id of the asset the task is linked to. (pid and gid are irrelevant, they're just used to group the assets).

Each asset has one or more tasks associated with it. What I want to do is calculate how many % of the tasks are complete on each asset. Something like:

SELECT asset.name, COUNT(task1.status) AS task_completed
FROM task AS task1
LEFT JOIN asset
ON (task1.aid=asset.id AND asset.pid=3 AND asset.gid=2 AND task1.status='completed')
WHERE asset.gid=2 AND asset.pid=3
GROUP BY asset.name

Gives me the name of each task with the number of completed tasks. Now I want to compare that number to the notal number of tasks and figure out the percentile of completed tasks for each asset. Something like this:

SELECT name, ROUND((completed_tasks / task_count) * 100) AS `status_%`
FROM (

SELECT COUNT(task.status) AS completed_tasks
FROM task
WHERE task.status='completed'
AND aid=9
GROUP BY aid
) AS completed_tasks,

(SELECT COUNT(task.status) AS (all_tasks)
FROM task
WHERE aid=9
GROUP BY aid
) AS task_count

WHERE asset.pid =3
AND asset.gid =2
GROUP BY asset.name

This code works, except that aid=9 is hardcoded in the subquery, so it will show the status asset.id=9 for all assets. I need aid for each subquery to equal asset.id of the current row.

So, to summarize. :)

I have multiple tasks associated with assets. I need to get the percentile of tasks associated with each asset that has status set to 'completed'. How?