Need help with sql query

[Hussein s]

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;

---

---

[Bill Sheboy]

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]

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]

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