Lady_Campari
asked on
MySQL Percentage
I'm using MySQL 5.1, and I'm trying to calculate a percentage. Basically, I have a huge database with all the information regarding projects. What I'm trying to do is the following: all the user requirements are stored in the database. Some of them will be implemented through configuration and others through development. I'm trying to find out the percentage of user requirements that will be implemented through configuration. Of course, I need this information for all the projects.
So far, I came up with two queries. The first one lists all the project and the total number of user requirements per project:
SELECT project.name As 'Project_Name', COUNT(document.documentKey ) as 'Total_Requirements' FROM `contour`.`document`
inner join project
on document.projectID=project .id
where document.documentKey like 'UR%'
and document.active = 'T'
and document.isFolder = 'F'
and document.lookup3Id = '210'
group by project.id;
Result:
Project_Name Total_Requirements
Project 1 1
Project 2 3
Project 3 5
The second query lists the number of requirements, per project, that are implemented through configuration:
SELECT project.name As 'Project_Name', COUNT(document.documentKey ) as 'Config_Requirements' FROM `contour`.`document`
inner join project
on document.projectID=project .id
where document.documentKey like 'UR%'
and document.active = 'T'
and document.isFolder = 'F'
and document.lookup3Id = '210'
and document.lookup7Id = '440'
group by project.id;
Result:
Project_Name Config_Requirements
Project 2 1
Project 3 2
Now, I need to somehow join the results of these two queries to calculate the percentage. For project 1, we have 0 / 1; for project 2, 1 / 3; and for project 3, 2/5.
I'm just stuck here. Maybe there is an easier way of doing it, but I don't know much about SQL. Any help would be appreciated.
Thanks.
So far, I came up with two queries. The first one lists all the project and the total number of user requirements per project:
SELECT project.name As 'Project_Name', COUNT(document.documentKey
inner join project
on document.projectID=project
where document.documentKey like 'UR%'
and document.active = 'T'
and document.isFolder = 'F'
and document.lookup3Id = '210'
group by project.id;
Result:
Project_Name Total_Requirements
Project 1 1
Project 2 3
Project 3 5
The second query lists the number of requirements, per project, that are implemented through configuration:
SELECT project.name As 'Project_Name', COUNT(document.documentKey
inner join project
on document.projectID=project
where document.documentKey like 'UR%'
and document.active = 'T'
and document.isFolder = 'F'
and document.lookup3Id = '210'
and document.lookup7Id = '440'
group by project.id;
Result:
Project_Name Config_Requirements
Project 2 1
Project 3 2
Now, I need to somehow join the results of these two queries to calculate the percentage. For project 1, we have 0 / 1; for project 2, 1 / 3; and for project 3, 2/5.
I'm just stuck here. Maybe there is an easier way of doing it, but I don't know much about SQL. Any help would be appreciated.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER