Select multiple fields with AGGREGATE FUNCTION AND GROUP BY


#1

Hi,

I have the following query

SELECT META(p).id,count(e.pin_id) FROM pins AS p INNER JOIN users AS `u` ON KEYS `p`.user_id LEFT OUTER JOIN events AS e
ON KEY e.pin_id FOR p WHERE META(p).id='47a5c31953a6d0626ac6031974b396a4' GROUP BY META(p).id;

Which Works fine but when I add one or more field in the select query I get an error. I want to group my query only by ID, but when I run below I get error

SELECT u.name,META(p).id,count(e.pin_id) FROM pins AS p INNER JOIN users AS `u` ON KEYS `p`.user_id LEFT OUTER JOIN events AS e
ON KEY e.pin_id FOR p WHERE META(p).id='47a5c31953a6d0626ac6031974b396a4' GROUP BY META(p).id;

[
  {
    "code": 4210,
    "msg": "Expression must be a group key or aggregate: (`u`.`name`)",
    "query_from_user": "SELECT u.name,META(p).id,count(e.pin_id) FROM pins AS p INNER JOIN users AS `u` ON KEYS `p`.user_id LEFT OUTER JOIN events AS e\nON KEY e.pin_id FOR p WHERE META(p).id='47a5c31953a6d0626ac6031974b396a4' GROUP BY META(p).id;"
  }
]

#2

If query has GROUP BY or aggregates. The projection (also HAVING, ORDER BY) can only have Aggregates or group by columns or expression on previous too.

The reason is as follows:
{“a”:1, “b”:2}
{“a”:1, “b”:3}

SELECT b, COUNT(1) FROM default GROUP BY a;
When group by is there it needs to produce one row for distinct group. For same group value of a =1 there are two values of b, Query can't decide which value to produce and not allowed. This is SQL standards.
If you want you can do this
  SELECT MIN(b), COUNT(1) FROM default GROUP BY a;
  SELECT MAX(b), COUNT(1) FROM default GROUP BY a;
   SELECT ARRAY_AGG(b), COUNT(1) FROM default GROUP BY a;