nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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&gt; 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

Accepted answers

Norman Abramovitz

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

All comments

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

Norman Abramovitz

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

Saneth Kumar

Thanks for ur answers, they both worked

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

Dan Fredell1

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.