The count line at G1 and G2 milestone is not displayed in SQL

Jovan Aks
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
July 24, 2024

I wanted to add the following to my SQL query (I use Confluence->Table Transformator):   I wanted to output the total number of G1 projects and separately the total number for G2 projects. Can I still add this to my code? Now I only get a 1 for each line, but unfortunately that doesn't give me the total number of G1 or G2. I've been working on it for so long, please help me.

This is my current code : 

"

SELECT *, COUNT(*) AS 'Projekte', T1.'Key'

FROM

(SELECT

  CASE

            WHEN T1.'Labels' LIKE "%G1%"

                        THEN FORMATWIKI("{cell:bgColor=#00FF00}G1{cell}")         

           WHEN T1.'Labels' LIKE "%G2%"

                       THEN FORMATWIKI("{cell:bgColor=#FF0000}G2{cell}")

END AS 'Milestone', 'Key'

FROM T*) T

WHERE T.'Milestone' IS NOT NULL

GROUP BY T.'Milestone', 'Key'

ORDER BY Projekte ASC

"

30.0_CODE.png30.1_Result_Just_Ones.png

 

1 answer

1 vote
Stiltsoft support
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.
August 2, 2024

Hi @Jovan Aks ,

The Table Transformer macro is provided by our app that is Table Filter, Charts & Spreadsheets for Confluence. So, you may check this part of our documentation that shows how to count definite values in a column: https://docs.stiltsoft.com/tfac/dc-server/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-Countingthedefinitevalueinacolumn

If you are still stuck, you may refer to our support portal that is confidential. There you may share your page storage format (upper right corner of the page -> menu ... -> View storage format) and the screenshot of your Jira Issues table and we'll help you with the case.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events