Link to home
Start Free TrialLog in
Avatar of Lady_Campari
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.
ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lady_Campari
Lady_Campari

ASKER

Fast answer and seems to work great. Thanks a lot