MySQL Percentage

Question:

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.

Solution:

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;