Including counts with DISTINCT embedded in FORMATWIKI

Scott Gillespie
Contributor
October 19, 2023

This question is about how to combine some functions within Table Transformer SQL to add more information to a display table.

I have the following data table:

Engagement
Engagement Team
Escalation?
Watch List? (Yes or No)
Watch List Category

ABC

ABCNoNoResources
XYZXYZ 1NoYesFTL Issue
XYZXYZ 2YesYesResources

I use Table Transformer to create a summary table showing total number of engagements, engagement teams, number of engagement escalations ("Yes"), number of Watch List engagements ("Yes") and a list of the unique watch list categories, displaying this table:

 

Total Engagement Teams
Total Engagements
# Engagement Teams on Watch List
# Engagement Escalations
Active Watch List Categories
3221

FTL Issue

Resources 

The Table Transformer SQL is below and works fine. It would be helpful to show how many instances there are of each watch list category, such that the display would show in the last column, for example:

1 - FTL Issue

2 - Resources

In the last FORMATWIKI statement, I have tried many different ways to include COUNT to add the quantity to the DISTINCT categories, but it always fails, either not displaying anything or giving an error. I suspect I am simply not using the right syntax because the statement is rather complex, but I have run out of ideas on how to set up.

Please suggest if there is a way to accomplish this within the existing FORMATWIKI statement. I'm open to re-working the SQL if my current code is too inefficient or clumsy.

SELECT
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", COUNT('Engagement Team'), "{cell}")
AS 'Total Engagement Teams',
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", COUNT(DISTINCT 'Engagement'), "{cell}")
AS 'Total Engagements',
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", SUM(CASE WHEN 'Watch List? (Yes or No)' = "Yes" THEN 1 ELSE 0 END), "{cell}")
AS '# Engagement Teams on Watch List',
FORMATWIKI("{cell:width=250px|align=center|textColor=Red|font-size=50px|font-weight=bold}", SUM(CASE WHEN 'Escalation?' = "Yes" THEN 1 ELSE 0 END), "{cell}")
AS '# Engagement Escalations',
/* Display list of unique watch list categories currently in use */
FORMATWIKI("{cell:width=250px|font-size=20px|font-weight=bold}", (SELECT SUM(DISTINCT('Watch List Category') + " \n")
FROM T1 WHERE 'Watch List Category' <> "N/A"), "{cell}")
AS 'Active Watch List Categories'
FROM T1

1 answer

1 accepted

4 votes
Answer accepted
Stiltsoft support
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.
October 20, 2023

Hi @Scott Gillespie ,

The issue is that all your queries give you one number (one cell) and in the last cell you want to combine several numbers with an explanation (smth like aggregated table).

The workaround may be to reuse your source table with the help of the Table Excerpt/Table Excerpt Include macros and create a simple pivot table (you may use the Pivot Table macro for this purpose) and count how many of which categories you have.

Or if your categories are static (there are only specific names for them) you may count each one category separately in its own cell with its own header (for example, like you've count the Engagement Escalations).

Stiltsoft support
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.
October 20, 2023

An update from our side: if you want to keep the one table, you may use the following SQL query:

SELECT
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", COUNT('Engagement Team'), "{cell}")
AS 'Total Engagement Teams',
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", COUNT(DISTINCT 'Engagement'), "{cell}")
AS 'Total Engagements',
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", SUM(CASE WHEN 'Watch List? (Yes or No)' = "Yes" THEN 1 ELSE 0 END), "{cell}")
AS '# Engagement Teams on Watch List',
FORMATWIKI("{cell:width=250px|align=center|textColor=Red|font-size=50px|font-weight=bold}", SUM(CASE WHEN 'Escalation?' = "Yes" THEN 1 ELSE 0 END), "{cell}")
AS '# Engagement Escalations',
/* Display list of unique watch list categories currently in use */
FORMATWIKI("{cell:width=250px|font-size=20px|font-weight=bold}", (
SELECT SUM(DISTINCT('Watch List Category') + " \n")
FROM (
SELECT COUNT('Watch List Category') + " - " + 'Watch List Category' as 'Watch List Category'
FROM T1 WHERE 'Watch List Category' <> "N/A"
GROUP BY 'Watch List Category'
)
), "{cell}")
AS 'Active Watch List Categories'
FROM T1

Fri 3-1.png

Here we group the Watch List Category in the internal SELECT.

Like # people like this
Scott Gillespie
Contributor
October 21, 2023

This is EXACTLY what I was looking for, and it works perfectly!  Thank you very much for the patient guidance.  I had the right idea about a second SELECT clause, but I was getting it wrong how to embed it.

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events