Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Need help with sql query

Hussein s February 26, 2025 edited

I want a list of jiras created against each issue type under all the project from jan 2024. I am using below sql but it doesn't return list of those issue types where no jira was created from above mentioned date. 

 

SELECT
    p.pkey AS project_key,
    it.pname AS issue_type,
    COUNT(i.id) AS issue_count,
    COALESCE(au.lower_user_name, 'Unknown') AS project_lead, -- Project Lead (username from app_user table)
    COALESCE(cu.email_address, 'No Email') AS lead_email, -- Email of Project Lead (from cwd_user table)
    COALESCE(pc.cname, 'No Category') AS project_category, -- Project Category
    MAX(i.created) AS last_issue_created_date -- Last Issue Creation Date
FROM
    project p
JOIN
    jiraissue i ON i.project = p.id
JOIN
    issuetype it ON i.issuetype = it.id
LEFT JOIN
    app_user au ON p.LEAD = au.user_key -- Fetch project lead username
LEFT JOIN
    cwd_user cu ON au.lower_user_name = cu.lower_user_name -- Fetch project lead email
LEFT JOIN
    nodeassociation na_pc ON na_pc.source_node_id = p.id
    AND na_pc.association_type = 'ProjectCategory'
    AND na_pc.sink_node_entity = 'ProjectCategory' -- Link project to category
LEFT JOIN
    projectcategory pc ON na_pc.sink_node_id = pc.id -- Fetch project category name
WHERE
    p.pkey NOT LIKE 'Z-%'
    AND p.pkey NOT LIKE 'z-%'
    AND p.pkey NOT LIKE 'z - %'
    AND p.pkey NOT LIKE 'Z - %'
    AND p.pkey NOT IN ('BCS', 'DEVO', 'MCLS', 'SIOP')
    AND i.created >= '2024-01-01'
GROUP BY
    p.pkey, it.pname, au.lower_user_name, cu.email_address, pc.cname, p.id
ORDER BY
    p.id, it.pname -- Sorting by project ID, then by issue type;



 

1 answer

0 votes
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 26, 2025 edited

Hi @Hussein s -- Welcome to the Atlassian Community!

Jira Query Language (JQL) is not an SQL, and does not have many of the SQL features you are trying:

  • renaming fields with AS
  • COUNT
  • COALESCE
  • MAX
  • most wildcard LIKE expressions
  • any JOIN types
  • GROUP BY
  • etc.

To learn what is supported, please see this documentation: https://support.atlassian.com/jira-software-cloud/docs/use-advanced-search-with-jira-query-language-jql/

When you need additional query features, please investigate marketplace addons / apps, or export / pull the issues into another database tool which supports SQL.

Kind regards,
Bill

Hussein s February 26, 2025 edited

Hi Bill, I am using Jira DC & running sql in db. I am almost there except the thing which I have mentioned in question.

Not using jql as we have so many things restrictions such as security level & hence sql to get accurate count. 

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 26, 2025

My mistake; your question is indicated as "Cloud" which has no direct access to the database for such queries.

Like Hussein s likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
TAGS
atlassian, team '25, conference, certifications, bootcamps, training experience, anaheim ca,

Want to make the most of Team ‘25?

Spend the day sharpening your skills in Atlassian Cloud Organization Admin or Jira Administration, then take the exam onsite. Already ready? Take one - or more - of 12 different certification exams while you’re in Anaheim at Team' 25.

Learn more
AUG Leaders

Upcoming Jira Events