I have a requirement where there are six amount columns. I want to run a select query that calculates the sum of the values in six columns for each row (not the SUM() that aggregates the values in a column for all records) and use the result in a new column in select query.
Example records:
id col1 col2 col3 col4 col5 col6
1 2.0 3.0 2.3 3.4 5.3 66
2 2.0 3.0 2.0 3.0 5.0 66
What I need is like below:
id cal_amt
1 82.0
2 81
There isn't a way to do this natively in CQL.
The most convenient way is to handle this in you application such that it performs an aggregation on the target columns and only return the aggregate to the user.
Not sure which version of Cassandra you are on, but this is possible if you're on Cassandra 4.0 or Astra DB.
> SELECT id, col1 + col2 + col3 + col4 + col5 + col6 as cal_amt FROM calc_cols WHERE id IN (1,2);
id | cal_amt
----+---------
1 | 82.0
2 | 81.0
(2 rows)
One of the new features of Cassandra 4.0 is the implementation of arithmetic operators. I wrote an article detailing this new feature recently, and it can provide you with more details: