How to display multiple counts base on data from one table?

[Leona]

Hi,

I have one big table that I want to display counts of:

• Total Number of Rows
• Count of records with status "OPEN"
• Calculate Percentage of Done

I was able to populate Table A with three macros (see screenshot). But this solution is heavy and takes a long time to load as I have many other macros on the page. 

Any lightweight solution to look like Table B? Please note, I do not have TRANSWIKI.

Thank you.

[Srinatha T · Accepted solution]

HI @Leona ,

Welcome to Atlassian community. 

The below thread has some solution for you. It uses table transformer macro. 

• Table-Transformer-SQL-query-for-counting-values 

Try that. 

Thanks,

Srinath T

[Katerina Kovriga _Stiltsoft_]

Hi @Leona ,

As @Srinatha T has kindly mentioned, the similar case can be achieved with one Jira Issues macro and one Table Transformer macro.

SELECT *, 'Open'/'All'*100 AS '% of Completion'
FROM (SELECT SUM(IF(T1.'Status' = "To Do", 1, 0)) AS 'Open',
SUM(IF('Status' IN ("To Do", "In Progress", "Done", "Answered"), 1, 0)) AS 'All'
FROM T*)

To get a vertical table instead of the horizontal one go to the "Settings" tab of the Table Transformer macro and check the "Transpose result table" option.

Hope this helps your case.

[Leona]

Thank you both for the quick response. The solution works beautifully. 

I would use COUNT in the same way as SUM with the IF statement? When then would COUNT be a better solution than SUM? Thanks again.

[Katerina Kovriga _Stiltsoft_]

Hi @Leona ,

What do you mean by a better solution? I don't think that the change of the function will improve your performance or smth.

By the way you can check all the available functions here.

[Katerina Kovriga _Stiltsoft_]

And pay attention to the obtained results:

SELECT SUM(IF(T1.'Status' = "To Do", 1, 0)) AS 'Open' FROM T1

equals to the

SELECT COUNT('Status' = "To Do") AS 'Open' FROM T1 WHERE T1.'Status' = "To Do"

The simple changing of the function's name can lead to incorrect results (your conditions won't be taken into consideration).

You can find more use cases here.

[Leona]

Hi Katerina, which of the two would have a better performance from the two examples you provided? COUNT vs SUM? I am learning lots, thanks.

[Katerina Kovriga _Stiltsoft_]

The performance depends more on the number of issues that are returned by your Jira Issues macro and the number of other macros on top.

Here we have one Table Transformer, so only one SQL query will be applied to the source table. And the SUM function just makes the initial SQL query look more neat and easy-to-read.