Database query to check number of issues associated to a project

[Saneth Kumar]

I need to run a select query to check the number of issues in each project

This is what I used but it gave me the total number of issues in JIRA (constant) against each project

select jira.project.pname, count(jira.jiraissue.pkey) from jira.project, ji ra.jiraissue group by jira.project.pname;

Here's a snippet of the output

SQL> select jira.project.pname, count(jira.jiraissue.pkey) from jira.project, jira.jiraissue group by jira.project.pname;

PNAME
--------------------------------------------------------------------------------

COUNT(JIRA.JIRAISSUE.PKEY)
--------------------------
NCB Migration - FSRM Round2
                     17727

NCB Interim Solutions
                     17727

NCB Migration - CH_NOBOOK
                     17727


PNAME
--------------------------------------------------------------------------------

COUNT(JIRA.JIRAISSUE.PKEY)
--------------------------
NCB Performance IE7 - IE8
                     17727

NCB Migration - FSM1A
                     17727

NCB_Project1
                     17727


PNAME
--------------------------------------------------------------------------------

COUNT(JIRA.JIRAISSUE.PKEY)
--------------------------
NCB-UBS
                     17727

NCB Migration - FSRM
                     17727

NCB Migration - FSM2
                     17727

I guess there's something wrong with the query.

Thanks,

Saneth

[Norman Abramovitz · Accepted solution]

select count(*), p.pname from jiraissue i, project p where i.project = p.id group by p.pname, i.project;

FYI, the extra i.project in the group by is for projects that have the same name. I used MSSQL to verify the query syntax

Here is the place where I look for Jira sql queries.

https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA

[Maxfield B]

select count(*) as issuecount, p.pname from jiraissue i, project p where i.project = p.id group by p.pname, i.project order by issuecount desc;

this shows them in order of biggest project first.

[Divya Yathagiri Venkata]

this works but it doesn't show the projects which have zero issues

[Sathish reddy]

can we check number of tickets count in all projects at a time?

[Udo Brand]

Just run an empty jql. It will tell you how many issues are overall in your JIRA

[Bhushan Nagaraj]

Hi Saneth,

Can you try this and let me know if it works?

select p.key, COUNT(*) from jiraschema.project p

left join jiraschema.jiraissue i

on i.PROJECT = p.ID

GROUP BY p.pkey

[Jose Ramirez]

select ID, pkey,pname, pcounter from project where pcounter < 1

This gets all projects with the issue count 0. You can increment for projects with 1 issues, 2, etc...to check activity.

[Peter Vandenberghe]

Thanks works great!

[Dan Fredell]

I took a little different approch than the others. I wanted to see projects that had no tickets in them. This statement does a full join instad of an inner join.

select count(*) as "TicketCount", p.pkey

from project p

FULL OUTER JOIN jiraissue i

ON i.project = p.id

group by p.pkey, i.project

order by "TicketCount"

[Norman Abramovitz]

In the other queries I believe you can use the having clause

group by ....
having count(*) = 0

[Saneth Kumar]

Thanks for ur answers, they both worked :)