JQL query to find Distinct issues Edited

I have a custom field called Delta ID. Some of the user stories will have same ID

Foe Example 

ON-1243 will have Delta ID 51

ON-2341 will have Delta ID 51

ON-1687 will have Delta ID 51

ON-3456 will not have Delta ID

ON-2367 will have Delta ID 60

ON-1234 will not have Delta ID.

 

Now I need the count as 4 (User story with Delta ID 51+ 2(User story without Delta ID)+ User Story with Delta ID 60)

Now I need a gadget that will display the count of User story with Distinct Delta ID.

Suggest me a way to do this

4 answers

1 accepted

0 vote

Jira can't do this natively.   You are either going to have to export the search results into something like a CSV file and then use a tool like Excel or Numbers in order to sort/count the unique values there.

OR

perhaps you could use a plugin to Jira such as @Piotr Bojko has suggested.

 

But with Jira's native functionality, there is not a good way to find the number of issues with a specific unique value in JQL.  JQL is designed to return Jira issues, and it does that.  But it's not designed to allow you to perform these kinds of delta or summations with the issue results that are returned.   That feels like something you could probably do via SQL.  However Jira's native JQL cannot do this.

1 vote
Alana Fernando Community Champion Jun 07, 2018

@Saranya Unnikumar if you use Two-Dimensional Filter Statistics Gadget you can get a count of user stories per ID. 

if this is not what you are looking for, will you be able to elaborate your request more? 

Am using Two-Dimensional Filter Statistics Gadget, but it shows the User Story count as 3 in the above mentioned example.

I need count of User stories based on the distinct Delta ID custom field

Alana Fernando Community Champion Jun 07, 2018

have you select ,

  • Ticket type in XAxis or YAxis 
  • And Delta ID in XAxis or YAxis  

in your gadget setting? 

if so. it should show 3. as there are 3 stories that has "Delta ID 51". two dimensional filter gadget shows the ticket count that has added combinations in XAxis and YAxis. 

May I know why are you expecting user story count as 1 here? 

I would like to know if there is any JQL query that would filter user stories with distinct Delta ID

The count has to be 1 because all three user stories have same Delta ID. SO based on Delta ID , I would like to display count

Tarun Sapra Community Champion Jun 07, 2018

Hello @Saranya Unnikumar

What's the type of the custom field DeltaID? is it free text or drop-down?

Alana Fernando Community Champion Jun 07, 2018

@Saranya Unnikumar 

below JQL will filter out stories that contain a particulate Delta ID

type = Story AND Delta ID = Delta ID 51

Below JQL will filter stories that has a Delta ID

type = Story AND Delta ID is not empty 

Also two dimensional gadget should show count as 3. since there are 3 tickets that has the same Delta ID. Also It is not possible to select ticket key in XAxis or YAxis in two dimensional gadget. so it is not possible to get 1. 

@Alana Fernando So how can I get the user stories count for unique Delta ID value?

Tarun Sapra Community Champion Jun 07, 2018

Hello @Saranya Unnikumar

You are not framing your question right, that's why there is confusion.

You have mentioned in one of the comments

"The count has to be 1 because all three user stories have same Delta ID. SO based on Delta ID , I would like to display count"

And now you have written "So how can I get the user stories count for unique Delta ID value?"

You are writing that you want "user story count" but actually it seems you want the number of unique values of Delta ID being used across all the stories of your interest.

No Actually I need User story count for distinct Delta ID. Let me explain,

There are some user stories which have Delta ID as empty and for some stories the Delta ID is same. Now, in this case I want the User story count but the count must not include the user stories having same Delta ID.

Foe Example 

ON-1243 will have Delta ID 51

ON-2341 will have Delta ID 51

ON-1687 will have Delta ID 51

ON-3456 will not have Delta ID

ON-2367 will have Delta ID 60

ON-1234 will not have Delta ID.

 

Now I need the count as 4 (User story with Delta ID 51+ 2(User story without Delta ID)+ User Story with Delta ID 60)

Am I clear now?

Maybe this will help. By using this addon - https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview you can write following query

SELECT DeltaID, COUNT(KEY) CNT FROM TABLE(JQL('KEY,DeltaID',''))
GROUP BY DeltaID

With such query you should have following table:

 DeltaID | CNT

Delta ID 51 | 4

Delta ID | 2

Delta ID 60 | 1

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira Service Desk

How the Telegram Integration for Jira helps Sergey's team take their support efficiency to the bank

...+ reading Fantasy). The same is true for him at the bank he works for: Efficiency is key when time literally equals money. Read on to learn how Sergey makes most of the time he has by...

351 views 2 4
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