# Scylla Counters¶

Counters are useful for any application where you need to increment a count, such as keeping track of:

• the number of web page views on a website;
• the number of apps/updates downloaded;
• the number of clicks on an ad or pixel;
• the number of points earned in a game;

A counter is a special data type (column) that only allows its value to be incremented, decremented, read or deleted. As a type, counters are a 64-bit signed integer. Updates to counters are atomic, making them perfect for counting.

A table with a counter column is created empty and gets populated and initialized by updates, rather than inserts. A new record with a counter set to null is created on the first update statement with particular PK value and then immediately incremented by a given number (treating null as a 0). For example:

cqlsh:mykeyspace> CREATE TABLE cf (pk int PRIMARY KEY, my_counter counter);
cqlsh:mykeyspace> SELECT * FROM cf;
pk | my_counter
----+------------

cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter + 0 WHERE pk = 20;
cqlsh:cdc_test> SELECT * FROM cf;
pk | my_counter
----+------------
20 | 0

cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter + 6 WHERE pk = 0;
cqlsh:mykeyspace> SELECT * FROM cf;
cqlsh:cdc_test> SELECT * FROM cf;
pk | my_counter
---+------------
0 | 6
20 | 0


However, counters have limitations not present in other column types:

• The only other columns in a table with a counter column can be columns of the primary key (which cannot be updated). No other kinds of column can be included. This limitation safeguards correct handling of counter and non-counter updates by not allowing them in the same operation.
• All non-counters columns in the table must be part of the primary key.
• Counter columns, on the other hand, may not be part of the primary key.
• Counters may not be indexed.
• Counters may not be part of a materialized view.
• One cannot use TIMESTAMP or set a TTL (time to live) when updating a counter.
• Once deleted, counter column values cannot be used again.
• Counters cannot be set to a specific value, other than when incrementing from 0 using the UPDATE command at initialization.
• Updates are not idempotent. In the case of a write failure, the client cannot safely retry the request.

## Example:¶

1. Create a table. The table can only contain the primary key and the counter column(s):

cqlsh:mykeyspace> CREATE TABLE cf (pk int PRIMARY KEY, my_counter counter);

2. Increment the count:

cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter + 3 WHERE pk = 0;
cqlsh:mykeyspace> SELECT * FROM cf;

pk | my_counter
---+-------------
0  | 3

3. Decrement the count:

cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter - 1 WHERE pk = 0;
cqlsh:mykeyspace> SELECT * FROM cf;

pk | my_counter
---+-----------
0  | 2

4. Delete the row:

cqlsh:mykeyspace> delete from cf where pk = 0;
cqlsh:mykeyspace> select * from cf;

pk | my_counter
---+-----------


Remember that once deleted, counter column values cannot be used again. Read our blog on counters, or see the data type description.