Database query to check number of issues associated to a project

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

This widget could not be displayed.

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

This widget could not be displayed.

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.

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

This widget could not be displayed.

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

This widget could not be displayed.

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

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

This widget could not be displayed.

Thanks for ur answers, they both worked :)

This widget could not be displayed.

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"

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

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

This widget could not be displayed.

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.

Thanks works great!

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted yesterday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

72 views 1 0
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you