Using Sum in CQL

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?

2 answers

1 vote

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]+)?$")

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

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?

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Like • Marc - Devoteam likes this

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