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.
Lady_CampariAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
try this
SELECT project.name As 'Project_Name', 
		COUNT(document.documentKey) as 'Total_Requirements', 
		SUM(CASE WHEN document.lookup7Id = '440' THEN 1 ELSE 0 END) as 'Config_Requirements',  
		SUM(CASE WHEN document.lookup7Id = '440' THEN 1 ELSE 0 END) / COUNT(document.documentKey) as 'Perc entage' 
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;

Open in new window

0
 
Lady_CampariAuthor Commented:
Fast answer and seems to work great. Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.