-3

I'm doing a report for Bug Metrics. Pertain to that I need some customized table but my query doesn't give expected output.

My query gives the output Current Output. So, I want the Oustanding column value must be grouped by project but it is calculating the other project value also.

**Current Output**
*********************************************
|Project   | Opened | Resolved| Outstanding |
*********************************************
|  A       |    1   |    0    |    1        |
*********************************************
|  A       |    2   |    1    |    2        |
*********************************************
|  A       |    1   |    2    |    1        |
*********************************************
|  B       |    3   |    2    |    2        |
*********************************************
|  B       |    2   |    1    |    3        |
*********************************************

I want the output like Expected Output, the Outstanding value should calculate separately for each project. Like say precisely, When it comes to Project 'B' it should show the only outstanding values belong to it not to add previous projects outstanding values.

**Expected Output**
*********************************************
|Project   | Opened | Resolved| Outstanding |
*********************************************
|  A       |    1   |    0    |    1        |
*********************************************
|  A       |    2   |    1    |    2        |
*********************************************
|  A       |    1   |    2    |    1        |
*********************************************
|  B       |    3   |    2    |    1        |
*********************************************
|  B       |    2   |    1    |    2        |
*********************************************

1) Column I want to group by - Outstanding

2) Incremental Variable - @runtot

3) Calculation used for Outstanding - (@runtot := (jtb.Opened + @runtot) - jtb.Resolved) AS Outstanding

My query: - 

SELECT jtb.Project,jtb.Opened,jtb.Resolved,
(@runtot :=  (jtb.Opened + @runtot) - jtb.Resolved) AS Outstanding FROM
(SELECT ji.project AS Project_Id, 
pr.pname AS Project_Name,
COUNT(DISTINCT(ji.ID)) AS Opened,
COUNT(IF(ji.issuestatus  = 5 OR ji.issuestatus = 6,ji.issuestatus,NULL)) AS Resolved  
FROM  jiraissue ji 
LEFT OUTER JOIN priority p ON (p.ID = ji.priority) 
LEFT OUTER JOIN project pr ON (ji.project = pr.ID)  
GROUP BY ji.project) jtb,
(SELECT @runtot:=0) c

Kindly help me out :)

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
V_instincts
  • 3
  • 5
  • 1
    Please read [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). Right now, there's some sample data missing from your question. Without it, it's really hard to know what you want from us. – waka Oct 04 '17 at 08:14
  • Now it is fine? Please let me know if changes required.Thanks for the url @waka. – V_instincts Oct 04 '17 at 08:29
  • The output on the pictures do not match the output of the query you tried, there are different fields in your query. Also, pls include the results as text, no as image and include sample data as well that produce the outcome you receive. – Shadow Oct 04 '17 at 08:45
  • Thanks @ Shadow, I have removed the extra columns in my query. For prompt understanding, I have uploaded an image but henceforth I will change while asking the question. – V_instincts Oct 04 '17 at 08:53

1 Answers1

0

At last, this question gave a solution. I have added one more global variable in my query for updating the project name. So, When a new project arrives in @prev_project, it will refresh the @runtot=0.

Query Works for me:-

SELECT jtb.Project,jtb.Opened,jtb.Resolved,
(@runtot :=  IF(jtb.Project = @prev_project, ((@runtot+jtb.Opened ) - 
jtb.Resolved),(jtb.Opened - jtb.Resolved))) AS Outstanding,
@prev_project := jtb.Project AS sample_project_name FROM
(SELECT ji.project AS Project_Id, 
pr.pname AS Project_Name,
COUNT(DISTINCT(ji.ID)) AS Opened,
COUNT(IF(ji.issuestatus  = 5 OR ji.issuestatus = 6,ji.issuestatus,NULL)) AS Resolved  
FROM  jiraissue ji  
LEFT OUTER JOIN project pr ON (ji.project = pr.ID)  
GROUP BY ji.project) jtb,
(SELECT @runtot:=0, @prev_project := NULL) c
V_instincts
  • 3
  • 5