Group by on select not working

Question

I have two tables, my outer select aggregates all those ids which are present in my other table which I have put in where clause. Both tables are generated using wild cards function. following is the query -

SELECT count(id), timestamp  FROM (TABLE_QUERY(dataset1, 'expr'))  
WHERE id IN (SELECT id FROM (TABLE_QUERY(dataset1, 'expr')) 
WHERE timestamp < 1414670361836)  ) group by timestamp

I get following error -

Query Failed Error: (L1:56): JOIN (including semi-join) and UNION ALL (comma) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.

Can someone point me out what is the problem and how can I resolve it.

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2014-10-30T18:21:39

Re-phrasing with a query working on public data.

This doesn't work:

SELECT COUNT(actor), created_at
FROM (TABLE_QUERY([publicdata:samples], "table_id CONTAINS 'github'"))
WHERE actor IN (
  SELECT actor
  FROM (TABLE_QUERY([publicdata:samples], "table_id CONTAINS 'github'"))
  WHERE created_at > '')  
GROUP BY created_at

Error: (L2:1): JOIN (including semi-join) and UNION ALL (comma) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.

This does:

SELECT COUNT(actor), created_at
FROM (
  SELECT actor, created_at
  FROM (TABLE_QUERY([publicdata:samples], "table_id CONTAINS 'github'"))
)
WHERE actor IN (
  SELECT actor
  FROM (TABLE_QUERY([publicdata:samples], "table_id CONTAINS 'github'"))
  WHERE created_at > ''
  LIMIT 100)  
GROUP BY created_at

I just moved the

  SELECT actor, created_at
  FROM (TABLE_QUERY([publicdata:samples], "table_id CONTAINS 'github'"))

to an inner query.

Group by on select not working

1 Answers