Database query to check number of issues associated to a project

Saneth Kumar May 25, 2012

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

7 answers

1 accepted

5 votes
Answer accepted
Norman Abramovitz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 26, 2012

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

4 votes
Maxfield B June 18, 2015

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 October 31, 2017

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

1 vote
Sathish reddy September 11, 2013

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

Udo Brand
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 11, 2013

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

1 vote
Bhushan Nagaraj May 25, 2012

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

0 votes
Jose Ramirez May 17, 2018

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 May 31, 2018

Thanks works great!

0 votes
Dan Fredell April 17, 2014

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 17, 2014

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

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

0 votes
Saneth Kumar June 10, 2012

Thanks for ur answers, they both worked :)

Suggest an answer

Log in or Sign up to answer