N1ql group by not working in 4.5 ,code 4210

SQL++
1

Trying with this query. This works like a charm in 4.1. But in 4.5 getting below error.

SELECT t.reservationStatus as status,count(*) as count FROM Transport t where t.selectedSlot.windowStart > ‘2016-12-10T05:00:00Z’ group by status;

[
{
“code”: 4210,
“msg”: “Expression must be a group key or aggregate: (t.reservationStatus)”,
“query_from_user”: “SELECT t.reservationStatus as status,count(*) as count FROM Transport t where t.selectedSlot.windowStart > ‘2016-12-10T05:00:00Z’ group by status;”
}
]

select t.reservationStatus as status, ARRAY_AGG ({“locationId”: t.consignment.shipFrom.locationId} ) from Transport t group by status

[
{
“code”: 4210,
“msg”: “Expression must be a group key or aggregate: (t.reservationStatus)”,
“query_from_user”: “select t.reservationStatus as status, ARRAY_AGG ({“locationId”: t.consignment.shipFrom.locationId} ) from Transport t group by status”
}
]

2

Try the following N1QL

SELECT t.reservationStatus as status,count(*) as count 
FROM Transport t 
where t.selectedSlot.windowStart > '2016-12-10T05:00:00Z' 
group by t.reservationStatus;

and

select t.reservationStatus as status, ARRAY_AGG ({"locationId": t.consignment.shipFrom.locationId} ) 
from Transport t 
group by t.reservationStatus
1 Like
3

Thanks yang it worked, but in my query too am grouping by alisa, so was expecting that to work . But i dint

4

GROUP BY expressions require a match with the projection expression and not their alias.

Thanks.

2 Likes