Using Sum in CQL

[Craig_Harley]

hi, i'm trying to sum a column, whose values are alphanumeric

i.e. can have 0, 1 12, 100, n/a and TBC, how do i exclude the character fields so i can sum the numbers?

[Aliaksei Mikhailau _Stiltsoft_]

Hello @Craig_Harley ,

As you mentioned the Table Transformer macro of our Table Filter, Charts & Spreadsheets app, let me propose the following type of solution:

SELECT
SUM(T1.'Your column name'::number) AS 'Numeric Sum'
FROM T1
WHERE MATCH_REGEXP(T1.'Your column name', "^[0-9]+(.[0-9]+)?$")

[Craig_Harley]

hi Alexey, this works perfectly on the first of 4 columns I am trying to Sum using this approach, however when i apply to the 2nd column, it does not give the correct result. I am additionally rounding to 2 decimal places

the SQL i am using is

SELECT
'Sum Transaction Totals',
ROUND (SUM (T1.'Transactions1'::number), 2) AS 'Peak Load (TPS)',
ROUND (SUM(T1.'Transactions2'::number), 2) AS 'Peak Load (TPM)'
FROM T1
WHERE MATCH_REGEXP(T1.'Transactions1', "^[0-9]+(.[0-9]+)?$")
AND MATCH_REGEXP(T1.'Transactions2', "^[0-9]+(.[0-9]+)?$")

any thoughts?

[Aliaksei Mikhailau _Stiltsoft_]

Hello @Craig_Harley ,

The initially proposed approach is for the only column. This approach doesn't work correctly for multiple columns because it filters entire rows. If one column has a non-numeric value, the whole row is excluded, even if the other column has valid data. As a result, valid values are skipped, and the totals are incorrect.

Using CASE WHEN MATCH_REGEXP(...) THEN ... ELSE 0 inside each SUM should allow independent filtering per column, ensuring accurate aggregation. Please try this SQL query:

SELECT

'Sum Transaction Totals',

ROUND(SUM(
CASE
WHEN MATCH_REGEXP('Transactions1' + "", "^[0-9]+(\.[0-9]+)?$")
THEN CAST('Transactions1' AS number)
ELSE 0
END
), 2) AS 'Peak Load (TPS)',

ROUND(SUM(
CASE
WHEN MATCH_REGEXP('Transactions2' + "", "^[0-9]+(\.[0-9]+)?$")
THEN CAST('Transactions2' AS number)
ELSE 0
END
), 2) AS 'Peak Load (TPM)'

FROM T1

[Marc - Devoteam]

Hi @Craig_Harley 

How would you see this, CQL is for text searching.

I can't see the sum option in using CQL

[Craig_Harley]

let me re-phrase, I am using SQL in table Transformer from Table Filter and charts

[Marc - Devoteam]

Hi 

To sumup, see this doc.

I don't think there are options to exclude.

https://docs.stiltsoft.com/tfac/cloud/calculations-in-tables-42239860.html 

You could raise this as the app developer's support portal.