It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JQL query to find Distinct issues Edited

Saranya Unnikumar Jun 07, 2018

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 votes
Answer accepted
Andrew Heinzer Atlassian Team Jun 07, 2018

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 Leader 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? 

Saranya Unnikumar Jun 07, 2018

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 Leader 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? 

Saranya Unnikumar Jun 07, 2018

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

Saranya Unnikumar Jun 07, 2018

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 Leader 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 Leader 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. 

Saranya Unnikumar Jun 07, 2018

@Tarun Sapra It is free text

0 votes
Saranya Unnikumar Jun 07, 2018

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

Tarun Sapra Community Leader 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.

Saranya Unnikumar Jun 07, 2018

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?

0 votes
Piotr Bojko Jun 07, 2018

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

Saranya Unnikumar Jun 07, 2018

Okay Thanks!!

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Next-gen

Introducing subtasks for breaking down work in next-gen projects

Teams break work down in order to help simplify complex tasks. This is often done iteratively, with tasks being broken down into smaller tasks and so on until the work is accurately captured in well-...

988 views 12 15
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you