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; } } }

JQL query to find Distinct issues

saranya22

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

Accepted answers

Piotr Bojko

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

Andy Heinzer

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.

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.

All comments

Alana Fernando

@saranya22 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?

saranya22

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

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?

saranya22

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

saranya22

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

Tarun Sapra

Hello @saranya22

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.

saranya22

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?

Piotr Bojko

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

Andy Heinzer

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

saranya22

Okay Thanks!!

Ashwin Kumar H M

@Piotr Bojko - Thanks for your suggestion. I am badly stuck up in trying to get the unique values. In desperate need of some solution. Wanted to try your suggestion. I am using latest Jira. Extremely disappointed with the UI. Can you please let me know where can I find the Manage Add-on on the new Jira. I am the Admin and couldn't find mention of Add-on anywhere.

Piotr Bojko

Yeah

Follow the screens: Screenshot (4).png

Screenshot (5).png Screenshot (6).png

Contact Kintosoft

https://community.atlassian.com/t5/Jira-questions/JQL-distinct-or-unique-clause/qaq-p/1097192

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server