database - Sql query to perform Load check for an Employee

Question

I have a table design like this ...

• 1 Project--> Multiple products.
• 1 Product--> Multiple environment.
• 1 Employee--> Multiple activities.

Status can be = "new", "inprogress", "on hold", "closed"

    Activity table- ActID(PK), assignedtoID(FK), Env_ID(FK), Product_ID(FK), project_ID(FK), Status. 
    Product_table - Product_ID(PK), Product_name
    Project_Table- Project_ID(PK), Project_Name. 
    Environment_Table- Env_ID(PK), Env_Name. 
    Employee_Table- Employee_ID(PK), Name.
    Employee_Product_projectMapping_Table -Emp_ID(FK), Project_ID(FK), Product_ID(FK).
    Product_EnvMapping_Table - Product_ID(FK), Env_ID(FK). 

Load check query

First find out the total number of activities assigned to each employee in a particular project.

(A). then find the number of activities for that employee in that project where status = new or inprogress. (B). Divide A/B. Compute this A/B in percentage value.

So my final query table output will consist of

• Name of employee
• name of project
• total assigned act(Count value)
• Act having status new or in process(Count)
• Load(the percentage value. )

Can anyone help me with the query and explain how I can achieve this result? Thanks.

So far i have tried this much ... getting the results of A and B but now i need to combine these two...

(A).

SELECT activity_table.assignid, activity_table.projid, Count(activity_table.actid) AS CountOfactid
FROM activity_table
GROUP BY activity_table.assignid, activity_table.projid;

(B).

SELECT activity_table.assignid, activity_table.projid, Count(activity_table.actid) AS CountOfactid
FROM activity_table where status in ('new','inprogress')
GROUP BY activity_table.assignid, activity_table.projid ;

Answer 1

This is Upto 3rd result...not for 4th one.

select Employee_table.name,Project_table.Project_Name, Count(activity_table.actid) AS CountOfactid

from Activity table,Employee_Table,Project_Table,Employee_Product_projectMapping_Table

where Activity_Table.Product_Id=Project_Table.Product_Id

 and 

aage ka syntax yaad nai aa raha hai...

Employee_Product_projectMapping_Table aur  Employee_Table ko combine karke emp name through equating product id...

for the 4th one use nested query......

will try to solve this query asap.....:P

409008....