Open In App

Aggregate functions in Cassandra

Improve
Improve
Like Article
Like
Save
Share
Report

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 

Last Updated : 01 Nov, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads