Aggregate functions in Cassandra

In this article, we will discuss Aggregate functions in Cassandra which can be used for various purposes such that to count, for finding min and max, sum, etc.

Aggregate functions:

1. Count
2. Max and Min
3. Sum 
4. Avg 

In Cassandra, these aggregate functions are pre-defined or in-built functions. Aggregate functions in Cassandra work on a set of rows. Aggregate functions receive values for each row and then return one value for the whole set. If normal columns, scalar functions, UDT fields, WriteTime or TTL(Time To Live) are selected together with aggregate functions, the values returned for them will be the ones of the first row matching the query.



Let’s discuss with example:

To create the keyspace used the following CQL query.

CREATE KEYSPACE test1 with replication =
{‘class’ : ‘SimpleStrategy’, ‘replication_factor’ : 1} ; 

To use the test1 keyspace used the following CQL query.

USE test1; 

To create the table used the following CQL query.

CREATE TABLE Emp_record
 (
  E_id int PRIMARY KEY,
  E_score int,
  E_name text,
  E_city text
 ); 

To insert the values into table Emp_record used the following CQL query.

INSERT INTO Emp_record(E_id, E_score, E_name, E_city) 
       values (101, 85, ‘ashish’, ’Noida’);
INSERT INTO Emp_record(E_id, E_score, E_name, E_city) 
       values (102, 90, ‘ankur’, ’meerut’);
INSERT INTO Emp_record(E_id, E_score, E_name, E_city) 
       values (103, 99, ‘shivang’, ’gurugram’);
INSERT INTO Emp_record(E_id, E_score, E_name, E_city) 
       values (104, 85, ‘abi’, ’meerut’);
INSERT INTO Emp_record(E_id, E_score, E_city) 
       values (105, 95, ’mumbai’); 

To see the output used the following CQL query.

Select * 
from Emp_record; 

E_id E_score E_name E_city
101 85 Ashish Noida
102 90 Ankur sharma meerut
103 95 Shivang Gurugram
104 85 Abishek Rana meerut
105 95 null mumbai

1. Count:
The count function is used to count the rows returned by a query.

Example:


SELECT COUNT(*) 
FROM Emp_record; 

Alternatively, To get the same result we can use COUNT(1).

SELECT COUNT(1) 
FROM Emp_record; 

Output:

5 

It also can be used to count the non null value of a given column.

Example:

SELECT COUNT(E_name) 
FROM Emp_record; 

Output:

4 

2. Max and Min:
The Max function is used to compute the maximum value returned by a query for a given column.
The Min function is used to compute the minimum value returned by a query for a given column.

  • Example-1:
    SELECT MIN(E_score) 
    FROM Emp_record; 

    Output:

    85 
  • Example-2:
    SELECT MAX(E_score) 
    FROM Emp_record; 

    Output:

    95 

3. Sum:
The sum function is an aggregate function which can be used to sum up all the values returned by a query for a given column.

Example:

SELECT SUM(E_score) 
FROM Emp_record; 

Output:

450 

4. Avg:
The avg function is an aggregate function which can be used to compute the average of all the values returned by a query for a given column.

Example:

SELECT AVG(E_score) 
FROM Emp_record; 

Output:

90 


My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.