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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
this works but it doesn't show the projects which have zero issues
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
can we check number of tickets count in all projects at a time?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Just run an empty jql. It will tell you how many issues are overall in your JIRA
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
In the other queries I believe you can use the having clause
group by ....
having count(*) = 0
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.