[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

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.
0
Lady_Campari
Asked:
Lady_Campari
1 Solution
 
tigin44Commented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now