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;


NCB Migration - FSRM Round2

NCB Interim Solutions

NCB Migration - CH_NOBOOK


NCB Performance IE7 - IE8

NCB Migration - FSM1A




NCB Migration - FSRM

NCB Migration - FSM2

I guess there's something wrong with the query.



7 answers

1 accepted

3 votes
Accepted answer

select count(*), p.pname from jiraissue i, project p where i.project = 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.

select count(*) as issuecount, p.pname from jiraissue i, project p where i.project = 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

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

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

Thanks for ur answers, they both worked :)

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 =

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

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, check activity.

Thanks works great!

Suggest an answer

Log in or Sign up to answer
Community showcase
Published yesterday in Jira

How you can achieve compact and easy-to-maintain workflows in your JIRA( Server)

This approach requires you to have the JIRA administrative rights. The main aim of this article is to help you achieve an organized, easy-to-maintain workflows in your JIRA instance thereby, reducin...

189 views 0 0
Read article

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