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

Samata Sabala

My table transformer is giving me the below output,

Key	Objective	Epic	Team/s	Fix Version/s
ABC-1	Objective 1	Epic 1	Sharky Spark
ABC-1	Objective 1	Epic 2
ABC-6	Objective 6	Epic 5	Spark
ABC-9	Objective 9	Epic 10	Anvil Phoenix Vortex	2022.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-1	Objective 1	1	2
ABC-6	Objective 6	0	1

Katerina Kovriga {Stiltsoft}

Hi @Samata Sabala,

Please try the following SQL query:

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'

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'