Count basic , error in group

[Vector]

i try to run this query and get the result below i already try to add a name to the fields , but the error still appers any idea??

SELECT COUNT(order.system_id) AS count,
order.system_id AS syst,
MILLIS_TO_UTC(order.received_date_time,‘year’) AS years
FROM order
WHERE javaClass =
GROUP BY 2,
3
ORDER BY 2,
3

but i get this error {
“errors”: [
{
“code”: 4210,
“msg”: “Expression (order.system_id) (near line 2, column 15) must depend only on group keys or aggregates.”,
“query_from_user”: “explain SELECT COUNT(order.system_id) AS count,\n order.system_id AS syst,\n MILLIS_TO_UTC(order.received_date_time,‘year’) AS years\nFROM order\nWHERE javaClass = ‘’\nGROUP BY 2,\n 3\nORDER BY 2,\n 3”
}
]
}

[dh]

You have to list your terms - you can’t use ordinals for GROUP BY and ORDER BY. Try:

SELECT COUNT(`order`.system_id) AS count,
       `order`.system_id AS syst,
       MILLIS_TO_UTC(`order`.received_date_time,'year') AS years
FROM `order`
WHERE javaClass = ...
GROUP BY `order`.system_id, MILLIS_TO_UTC(`order`.received_date_time,'year')
ORDER BY `order`.system_id, MILLIS_TO_UTC(`order`.received_date_time,'year')

Or better:

SELECT COUNT(`order`.system_id) AS count,
       `order`.system_id AS syst,
        years
FROM `order`
LET years = MILLIS_TO_UTC(`order`.received_date_time,'year')
WHERE javaClass = ...
GROUP BY `order`.system_id, years
ORDER BY `order`.system_id, years

Ref: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/groupby.html#syntax

HTH.

[MilesWeb]

This error seems non-aggregate expression used in the SELECT clause. Below is the corrected query,

SELECT COUNT(order.system_id) AS count,
       order.system_id AS syst,
       MILLIS_TO_UTC(order.received_date_time, 'year') AS years
FROM order
WHERE javaClass = '' -- Assuming you want to filter by a specific javaClass
GROUP BY order.system_id,
         MILLIS_TO_UTC(order.received_date_time, 'year')
ORDER BY order.system_id,
         MILLIS_TO_UTC(order.received_date_time, 'year')

Instead of using numbers (2, 3), use expressions (order.system_id, MILLIS_TO_UTC(...))