How to create a summarize table based on columns of a table in confluence?

[Ross]

I have a table like this 

and I want a small summarized pivot table like shown below based on the above table :

  

How we can achieve this via confluence ? Is there any macro for this ?

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Ross ,

There are no such macros out-of-the-box, you'll need some additional apps to complete your case.

In this thread I suggested using our app - Table Filter and Charts for Confluence (it seems that it was also your question judging by the username).

And here is another bunch of screenshots for you: wrap your table into the Table Transformer macro.

SELECT "Summary" AS '',
SUM(IF(T1.'Column A' = "Yes", 1, 0)) AS 'Column A Yes',
SUM(IF(T1.'Column A' = "No", 1, 0)) AS 'Column A No',
SUM(IF(T1.'Column B' = "Yes", 1, 0)) AS 'Column B Yes',
SUM(IF(T1.'Column B' = "No", 1, 0)) AS 'Column B No',
SUM(IF(T1.'Column C' = "Yes", 1, 0)) AS 'Column C Yes',
SUM(IF(T1.'Column C' = "No", 1, 0)) AS 'Column C No'
FROM T1

Hope this helps.

[Ross]

Excellent answer @Katerina Kovriga {Stiltsoft} . Thank you very much.

[Katerina Kovriga {Stiltsoft}]

@Ross  And here is another more precise SQL query for your case:

SELECT "Yes" AS 'Count',
SUM(IF(T1.'Column A' = "Yes", 1, 0)) AS 'Column A',
SUM(IF(T1.'Column B' = "Yes", 1, 0)) AS 'Column B',
SUM(IF(T1.'Column C' = "Yes", 1, 0)) AS 'Column C'
FROM T1
UNION ALL CORRESPONDING
SELECT "No" AS 'Count',
SUM(IF(T1.'Column A' = "No", 1, 0)) AS 'Column A',
SUM(IF(T1.'Column B' = "No", 1, 0)) AS 'Column B',
SUM(IF(T1.'Column C' = "No", 1, 0)) AS 'Column C'
FROM T1

Now you don't need to transpose tables and your result table is neat and easy to read.