Counting columns that are not PK - Cassandra

Question

So I'm running with the problem of not being able to select a count of rows in my table.

My problem is: "Get the most reserved book in a library"

The model I created:

CREATE TABLE library_reservations (
     book_isbn bigint,
     book_title text,
     book_publicationdate date,
     reservation_date date,
     reservation_addinfo text,
     PRIMARY KEY(book_isbn)
);

The query:

SELECT book_isbn, book_title, COUNT(reservation_date) 
FROM library_reservations 
GROUP BY book_isbn;

I really feel like I got my modeling wrong. The table was an adaptation of PostgreSQL tables. So how can I properly get the count of reservations for each book? What shoud be my PK in this case?

Answer 1

Unfortunately Cassandra does not have a GROUP BY function.

A possible solution would be to maintain a second table with a counter.

Here you can find some more info regarding counters.

In your particular case, when a new reservation is made you will have to update also the counter table info.

Another thing to have in mind is that tables in Cassandra are designed to satisfy a certain query.