How to use two SQL requests (mysql) to calculate an average number

I want to calculate average number of re-opened tickets by project,but I couldn't do that with a single SQL request . I have retrieved the total number of tickets:

select count(jiraissue.id) as totalTicketByProj
from jiraissue ,project
where jiraissue.project=pro开发者_开发技巧ject.id
group by project.pname;

Also I have retrived number of re-opened tickets:

select count(changeitem.id) as nbissueReopenByProject
from changeitem,changegroup,jiraissue,project
where changeitem.groupid=changegroup.id
and changegroup.issueid=jiraissue.id
and jiraissue.project= project.id
and changeitem.oldstring="Resolved"
and changeitem.newstring="Closed"
group by project.pname;

But I don't know how to calculate the average number of reopened tickets (nbissueReopenByProject/totalTicketByProj) using a single request!!

Any Idea!!

---

SELECT (nbissueReopenByProject / totalTicketByProj)
  FROM (SELECT project.pname, COUNT(jiraissue.id) AS totalTicketByProj
          FROM jiraissue, project
         WHERE jiraissue.project = project.id
         GROUP BY project.pname) ttbp,
       (SELECT project.pname, COUNT(changeitem.id) AS nbissueReopenByProject
          FROM changeitem, changegroup, jiraissue, project
         WHERE changeitem.groupid = changegroup.id
           AND changegroup.issueid = jiraissue.id
           AND jiraissue.project = project.id
           AND changeitem.oldstring = "Resolved"
           AND changeitem.newstring = "Closed"
         GROUP BY project.pname) nbirbp
 WHERE ttbp.pname = nbirbp.pname

---

I've had to reform your query to use explicit joins, making a LEFT OUTER JOIN easier to form.

Once you have the LEFT OUTER JOINs in there, you can wrap a SUM around conditional statements... (My code may assume 1:1 relations in certain places as your questions doesn't have the schema details, but I trust this atleast helps you move toward your goal.)

SELECT
  COUNT(DISTINCT jiraissue.id)
  /
  SUM(CASE WHEN changeitem.oldstring="Resolved" and changeitem.newstring="Closed" THEN 1 ELSE 0 END)
FROM
  (
    jiraissue
  INNER JOIN
    project
      ON jiraissue.project= project.id
  )
LEFT JOIN
  (
    changeitem
  INNER JOIN
    changegroup
      ON changeitem.groupid=changegroup.id
  )
    ON changegroup.issueid=jiraissue.id
GROUP BY
  project.pname
;