"Cannot have aggregate and non-aggregate selectors in query" in azure cosmos cassandra db

Question

https://howtoprogram.xyz/2017/02/18/using-group-apache-cassandara/

I am trying to execute the same example which is in above link in azure cosmos cassandra db but getting the below error enter image description here

CREATE TABLE temperature_by_day (
   weatherstation_id text,
   date text,
   event_time timestamp,
   temperature float,
   PRIMARY KEY ((weatherstation_id,date),event_time)
);


INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-03','2016-04-03 07:01:00',73);
 
INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-03','2016-04-03 07:02:00',70);
 
INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-04','2016-04-04 07:01:00',73);
 
INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-04','2016-04-04 07:02:00',74);


SELECT weatherstation_id, date, MAX(temperature) FROM temperature_by_day GROUP BY weatherstation_id, date;

getting an error for the above query using group by

[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]

Can anyone help with this issue?

enter image description here I am expecting this result.

Answer 1

FWIW, it looks like this question was answered on Microsoft's site: https://learn.microsoft.com/en-us/answers/questions/1377555/cannot-have-aggregate-and-non-aggregate-selectors

The error message "Cannot have aggregate and non-aggregate selectors in query" in Azure Cosmos Cassandra DB typically occurs when you are trying to execute a query that contains both aggregate and non-aggregate selectors.

In Cassandra, you cannot mix aggregate functions (such as COUNT, SUM, AVG, MIN, MAX) with non-aggregate functions (such as SELECT, WHERE, ORDER BY) in the same query. This is because aggregate functions operate on groups of rows, while non-aggregate functions operate on individual rows.

They must be referring to Azure Cosmos Cassandra, as I can run all of the CQL in the OP's question on Apache Cassandra® 3.11 just fine.

Aggregate functions are supported in Azure Cosmos DB for SQL API and MongoDB API, but they are not supported for Cassandra API and Table API.

And the core problem, is that there is indeed a difference between Azure Cosmos Cassandra and Apache Cassandra. Remember folks, just because a cloud provider has the word "Cassandra" or "Keyspaces" in the name of their offering, doesn't mean that it's 100% compatible with Apache Cassandra.