1

ENCOUNTERED ERROR

When I execute the following GROUP BY clause:

SELECT month, title, country, director, sum(sum_taps), avg(viewing) FROM taps GROUP BY month, title, country, director;

I retrieve the following error:

SyntaxException: <Error from server: code=2000 [Syntax error in CQL query] message="line 1:78 no viable alternative at input 'GROUP' (...), avg(viewing) FROM [taps] GROUP...)">

HOW THE TABLE IS CREATED

The table is created in the following way:

CREATE TABLE taps (Month text, Title text, Country text, Director text, Viewing float, sum_taps float, PRIMARY KEY (Month,Title,Country,Director));

Where the data has been imported from a CSV file that has six columns: month, title, director, country, viewing_percentage and total_viewings. The names I am providing do not match the column names so that they are more understandable. This file is related to films purchased for certain customers for a given month. We have to perform analytics on the viewing statistics.

DESCRIPTION OF THE TABLE

Describe table output:

CREATE TABLE assignment.taps (
    month text,
    title text,
    country text,
    director text,
    sum_taps float,
    viewing float,
    PRIMARY KEY (month, title, country, director)
) WITH CLUSTERING ORDER BY (title ASC, country ASC, director ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

Any ideas on what I might be doing wrong?

Thanks in advance

lagger
  • 11
  • 1
  • Does this answer your question? [MAX(), DISTINCT and group by in Cassandra](https://stackoverflow.com/questions/17342176/max-distinct-and-group-by-in-cassandra) – ernest_k Nov 06 '19 at 17:16
  • I created your above table, and tried your query. It should work. What version of Cassandra are you using? It seems you may need to be on Cassandra 3.10 or higher. – Jim Wartnick Nov 06 '19 at 19:37
  • 1
    Hello @JimWartnick I can acknowledge that the issue was what you suggested. I updated Cassandra to 3.11 and it worked. Thanks a lot! – lagger Nov 07 '19 at 14:40
  • I've copied my comment into the "answer" so that others may see the answer as well. – Jim Wartnick Nov 07 '19 at 19:20

1 Answers1

0

I created your above table, and tried your query. It should work. What version of Cassandra are you using? It seems you may need to be on Cassandra 3.10 or higher.

Jim Wartnick
  • 1,974
  • 1
  • 9
  • 19