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
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:
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!
Hi @Esteban Borja Lopez ,
I can suggest using the following SQL query for the initial case:
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
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.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
2) Change the SQL query to "separate" the SUM and IF functions
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)
Hope it helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SUM(IF()) appears to be working correctly in my queries now (Confluence Cloud). Thank you!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The fix was also rolled out for Server/Data Center (version 9.6.1).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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?
Thank you!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Also you may try to use the Table Spreadsheet macro - it will do the trick in Excel-like style.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.