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

[Andy Heinzer · Accepted solution]

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.

[Piotr Bojko · Accepted solution]

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

[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:

[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]

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]

Hello @saranya22

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

[Alana Fernando]

@saranya22 

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. 

[saranya22]

@Tarun Sapra It is free text

[Dima Astahov]

its' April 2024,  everything moved into the cloud. And looks like there is no solution for Cloud, SmartQL - Power BI & Tableau & SQL by Piotr works only on server.

[Pablo Beltran _Marketplace Expert_]

@Dima Astahov

Not exactly 😉

If you are asking about SQL, then Ask Jira can do the job as it suppottd native SQL on Jira Cloud. 

In addion, it supports JQL in SQL too.

Disclamimer: I work at the company that developed Ask Jira. 

FYI, we were the first vendor supporting SQL natively on Jira Server in 2016 year, and on DC later, wirh a quite polular app named SQL+JQL. 

Ask Jira looks like a AI app, but it is really a powerful SQL tool. We trained ChatGPT to code native SQL in Jira Cloud. So you can type your quesiton in natural languaje and get a SQL statement in a few seconds, built by the AI. - > Click on the Edit button, and you will see the built SQL with syntax highlighting and code autocompletion (Ctrl+Space).

You can write and run your query from the scratch too, BUT if the query do has errors then the AI will fix it au5omatically behind the scene.

In other words,.the AI is always present building SQL queries from the scratch or fixing wrong ones.

There are more hidden features, but I cannot write a user manual here  😀

Pablo

[Pablo Beltran]

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

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