Table Transformer: SQL query for counting values

Esteban Borja Lopez

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!

 

 

Katerina Kovriga {Stiltsoft}

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.