Table Transformer: SQL query for counting values

Esteban Borja Lopez March 14, 2022

Hello community, 

im struggling with a basic operation for counting the amount of values on a given column aiming to get the total value, however the result by using COUNT or SUM return weird data I don't understand the logic behind it.

Basically, I want to get the amount of repetitions of value "Compliance" I have for column 'response time sla'. The example below shows the correct number (i.e. "3") but instead of getting the result in a single cell, I got it as many rows the table has (i.e. "9"). This is the SQL im using:

SELECT

(SELECT COUNT(TT3.'response time sla') FROM T3 AS TT3 WHERE TT3.'response time sla' = "Compliance") AS '[KPI-DBE.3]',

(SELECT COUNT(TT3.'response time sla') FROM T3 AS TT3) AS '[KPI-DBE.3 TOTAL]',

FROM T3 AS TT3

 

 

Screenshot 2022-03-14 at 09.25.34.png

Screenshot 2022-03-14 at 09.25.42.png

 

However, when im adding a new line to the SQL to get the SUM of all values on that column (by using "SUM" as an alternative to "COUNT") I got this:

 

Screenshot 2022-03-14 at 09.26.28.png

Screenshot 2022-03-14 at 09.26.28.png

 

What should be the right sentence to use to get just "3" or "9" for above queries?


 

In addition, taking advantage of the thread, let me ask 2 basic questions about SQL:

1. How can I add a comment on the SQL box to define some instructions about the purpose of the query so reader can understand what it does? I was not able to find any reference on the documentation.

2. How can I create an int or chat variable and allocate some calculation so I can use the variable along the SQL instead of copy+pasting all the time whole calculation sentence?

 

Thanks very much in advance for your support!

 

 

1 answer

1 accepted

2 votes
Answer accepted
Katerina Kovriga {Stiltsoft}
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.
March 14, 2022

Hi @Esteban Borja Lopez ,

I can suggest using the following SQL query for the initial case:

Mon 2-1.png

SELECT "Number of entries" AS '',
SUM(IF(T1.'Project' = "Project 1", 1, 0)) AS 'Project 1',
SUM(IF(T1.'Project' = "Project 2", 1, 0)) AS 'Project 2',
SUM(IF(T1.'Project' = "Project 3", 1, 0)) AS 'Project 3'
FROM T1

Mon 2-2.png

And you may also check this Community thread for more details.

Don't forget that you may use the Pivot Table macro to count the number of entries with a more friendly UI (the unwanted rows can be filtered further).

What concerns the first additional question, the Table Transformer macro is based on the AlaSQL library. So, you may leave comments in the following way:

/*Comment goes here*/

You may also try to use plain Confluence text/headings before/under the table and use user-friendly column names ("Number of entries" for my example).

For the second question I can provide you with a link where it's described how to use variables inside the Table Transformer macro: here by variables I mean the contents of the cells used for the query. I'm afraid that it is not possible to store and reuse the whole chunks of SQL queries as for now.

Kevin Goff January 4, 2023

Hi @Katerina Kovriga {Stiltsoft} ,

For more than a year I have been using the SUM(IF()) approach as you described above to conditionally count rows, but a few weeks ago my SQL query suddenly stopped working. The query no longer returns a number (not the true count, not zero).

Does the SQL query from your March 14, 2022 post still work for you?
Was there a change in the Table Transformer product that broke this functionality? 

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.
January 4, 2023

Hi @Kevin Goff ,

Thanks for reaching out! We see that you've created a ticket via our support portal - we'll continue to investigate the issue there.

Seems that the functionality regarding this precise combination is indeed broken at the moment. So, until everything works as it is supposed again, please consider the following workarounds:

1) Use the Pivot Table macro instead of the Table Transformer macro

Wed 6-1.png

Wed 6-2.png

2) Change the SQL query to "separate" the SUM and IF functions

Wed 6-3.png

SELECT 'Number of entries',
SUM('Project 1') AS 'Project 1', SUM('Project 2') AS 'Project 2'
FROM (SELECT "Number of entries" AS '',
IF(T1.'Project' = "Project 1", 1, 0) AS 'Project 1',
IF(T1.'Project' = "Project 2", 1, 0) AS 'Project 2'
FROM T1)

Wed 6-4.png

Hope it helps.

Like # people like this
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.
January 5, 2023

Hi @Kevin Goff ,

The fix was released for Confluence Cloud (it may take 1-2 days to reach all the instances).

And we continue to work on Server/Data Center as well.

Like # people like this
Kevin Goff January 5, 2023

SUM(IF()) appears to be working correctly in my queries now (Confluence Cloud). Thank you!

Like Stiltsoft support likes this
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.
January 12, 2023

The fix was also rolled out for Server/Data Center (version 9.6.1).

Like # people like this
KUMIKO_PERCIVAL_EXT March 29, 2023

Hi, 
I am adding to this post as I have a similar need. 
My team is using a table with the following column where a mention is included in the same cell as a date. Their preference is to not separate the date out into another column as the table is already getting pretty wide.

VariableDictionaryTableRedacted.png

I am hoping to count the number of times a given user is mentioned across these column entries (ignoring the dates). So, I'm looking for a SUM IF 'Variable Added by & Date' contains a specific username.  

The following SQL query isn't working for me (I realize that this may contain many errors as I am new to SQL). Is there a fix or an alternative approach that I could look into?

TableTransformerRedacted.png

TableTransformerOutputRedacted.png

Thank you!

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.
March 29, 2023

Hi @KUMIKO_PERCIVAL_EXT ,

Try to replace "=" by "LIKE" in your SQL query:

not SUM(IF(T1.'Mention' = "%John Smith%", 1, 0)) AS 'John Smith'

but SUM(IF(T1.'Mention' LIKE "%John Smith%", 1, 0)) AS 'John Smith'

Then your query should be working fine.

Like # people like this
KUMIKO_PERCIVAL_EXT March 29, 2023

Thank you @Stiltsoft support, that worked perfectly!

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events