N1ql group by error n1ql error Expression must be a group key or aggregate

SQL++
1

Hi there:

I am writing a simple n1ql as this:

SELECT meta().id, subjectId, objectId, permissions FROM `Bucket` GROUP BY objectId

But couchbase (Version: 4.6.3-4136 Enterprise Edition) complains: Expression must be a group key or aggregate

No idea why it is wrong, any help is appreciated!

Longsheng

2

As in regular SQL to use GROUP BY you have to have some aggregated field in the SELECT clause. You can find list of aggregate functions in the docs: https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/aggregatefun.html

3

That makes a lot sense to me. thanks @avsej

4

If Query has GROUP BY, projected columns needs to be aggregates or group by expressions.

1 Like
5

@avsej @vsr1
I write this which does work,

SELECT objectId, count(*) AS num
FROM Bucket
GROUP BY objectId

But am I able to only return the result whose num>1?

Thanks a lot.

6 
SELECT objectId, count(*) AS num
FROM `Bucket`
GROUP BY objectId
HAVING count(*) > 1;
1 Like
7

Thank you, simple answer @vsr1