Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,295,647
Community Members
 
Community Events
165
Community Groups

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

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.project.png

1 answer

1 accepted

2 votes
Answer accepted
Srinatha T Atlassian Team May 26, 2022

HI @Leona ,

Welcome to Atlassian community. 

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

Try that. 

Thanks,

Srinath T

Hi @Leona ,

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

Fri 9-1.png

Fri 10-1.png

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.

Like # people like this

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.

Like Srinatha T likes this

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.

Like Srinatha T likes this

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.

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.

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.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

931 views 13 27
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you