Using Table Transformer to show counts per row based on cell value

Samata Sabala September 15, 2022

My table transformer is giving me the below output,

Key
Objective
Epic
Team/s
Fix Version/s
ABC-1Objective 1Epic 1Sharky Spark 
ABC-1Objective 1Epic 2  
ABC-6Objective 6Epic 5Spark 
ABC-9Objective 9Epic 10Anvil Phoenix Vortex2022.11.29

I would like to get a count on rows that have NULL values grouped by Objective column i.e.

Key
Objective
Unassigned Team/s
Unassigned Fix Version/s
ABC-1Objective 11 2
ABC-6Objective 60 1

1 answer

4 votes
Katerina Kovriga {Stiltsoft}
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.
September 16, 2022

Hi @Samata Sabala,

Please try the following SQL query:

Fri 7-1.png

SELECT T1.'Key',
SUM(IF(T1.'Team/s' IS NULL, 1, 0)) AS 'Unassigned Team/s',
SUM(IF(T1.'Fix Version/s' IS NULL, 1, 0)) AS 'Unassigned Fix Version/s'
FROM T1 GROUP BY T1.'Key'

Fri 7-2.png

Hope this helps your case.

 

UPD. Forgot about the "Objective" column: add it to the query if you want to show this data as well.

SELECT T1.'Key', T1.'Objective',
SUM(IF(T1.'Team/s' IS NULL, 1, 0)) AS 'Unassigned Team/s',
SUM(IF(T1.'Fix Version/s' IS NULL, 1, 0)) AS 'Unassigned Fix Version/s'
FROM T1 GROUP BY T1.'Key', T1.'Objective'

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events