Aggregate Functions: count, sum, avg, min, max

An aggregate function takes many rows and returns one value, like a count or a total. CQL has five of them:

FunctionReturns
count(*)The number of rows
sum(column)The total of a number column
avg(column)The average of a number column
min(column)The smallest value
max(column)The largest value

For this lesson we use a new table, orders. Each row is one order from a customer:

CREATE TABLE orders (
  customer text,
  order_date date,
  amount decimal,
  PRIMARY KEY (customer, order_date)
);

The partition key is customer. This means all orders of one customer are stored together, in a group called a partition. You can ask for them with WHERE customer = 'alice'. The next section explains this kind of key in detail.

Examples

How many orders does alice have?

SELECT count(*) FROM orders
WHERE customer = 'alice';

Output:

 count
-------
     4

(1 rows)

Her smallest and largest order:

SELECT min(amount), max(amount) FROM orders
WHERE customer = 'alice';

Output:

 system.min(amount) | system.max(amount)
--------------------+--------------------
              20.25 |             250.00

(1 rows)

Names like system.min(amount) are what Cassandra calls a result column when you don't name it. Use AS to give it a better name. This query finds the average order of bob:

SELECT avg(amount) AS average_order
FROM orders WHERE customer = 'bob';

Output:

 average_order
---------------
        173.00

(1 rows)

Bob has two orders, 300.00 and 45.99. The exact average is 172.995, and Cassandra shows it rounded to 173.00.

Aggregate inside one partition

Every query above has a WHERE on customer. That is on purpose. An aggregate over one partition reads rows that are stored together, so it is fast. You can also aggregate the whole table:

SELECT sum(amount) FROM orders;

Output:

 system.sum(amount)
--------------------
             901.24

(1 rows)

Warnings :
Aggregation query used without partition key

Cassandra runs it, but adds a warning. To get this one number, it had to read every row from every node. On a big table that is slow. Real apps usually keep totals like "revenue this year" in a separate table that is updated as new data comes in.

You may know GROUP BY from SQL. CQL has it too, but you can only group by primary key columns, so you will rarely use it.

In the exercise, find out how much alice has spent in total, and name the result column.