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

Question

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]

Answer 1

@Amrutha Thank you for reaching out.

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.

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.

In SQL API, you can use aggregate functions such as COUNT, SUM, AVG, MIN, and MAX in your queries. Here is an example:

SELECT COUNT(1) FROM c WHERE c.status = 'completed'

In MongoDB API, you can use aggregate functions such as $count, $sum, $avg, $min, and $max in your queries. Here is an example:

db.orders.aggregate([

{ $match: { status: "completed" } },

{ $group: { _id: null, count: { $sum: 1 } } }

])

Hope that helped you.

Regards,

Oury