CODEWITHSUNDEEP
cassandracql
Matt Stevens
Matt Stevens

Reputation: 1124

Counting instances in a column

I have the following table

mykey.mytable

id | user   | item
------------------
 0 | 'matt' | 'ball'
 1 | 'bob'  | 'bat'
 2 | 'bill' | 'fridge'
 3 | 'matt' | 'beer'

What I would like is a count of users, for example

user   | count
--------------
'matt' | 2
'bob'  | 1
'bill' | 1

How do I do this in Cassandra?

FWIW, I have experimented with count(*), batch, counter and using the result of one select in another. Documentation is not much help and my google foo is failing to find any examples. I'm getting a little frustrated!

Upvotes: 1

Views: 154

Answers (1)

Aaron
Aaron

Reputation: 57843

Unfortunately, Cassandra CQL does not have an aggregation framework. So while you could easily do this in a relational database with a GROUP BY and a COUNT, Cassandra has no such mechanism.

One of the main principles of non-relational (NoSQL) data modeling, is to build your tables to match your query patterns. If querying the number of user items is something you forsee needing to do, then you could create another table to hold that information:

CREATE TABLE userItemCount (
user text,
itemCount counter,
PRIMARY KEY (user)
);

Via CQL you could increment that counter like this (assuming that Matt gets another item):

UPDATE userItemCount SET itemCount=itemCount+1 WHERE user='matt';

Of course, the down-side of this, is that you'll need to build a mechanism to add/increment the appropriate data when an entry is stored in your original table.

For a video walk through on this and other Cassandra development concepts, check out the free Java/Cassandra class on Datastax Academy: https://datastaxacademy.elogiclearning.com. Session #2, Module 43 covers the use of "counter tables."

Upvotes: 2

Related Questions