Find average service time of top 3 queries

SQL++
1

I’m using CB 6.6.1. I tried this correlated subquery:

SELECT statement,
       COUNT(*) AS count,
       (
           SELECT ROUND(AVG(STR_TO_DURATION(serviceTime) / 1000000), 1)
           FROM system:completed_requests
           WHERE statement = cr.statement) AS avg_service_time_ms
FROM system:completed_requests cr
GROUP BY statement
ORDER BY count DESC
LIMIT 3

but I get this error:

[
  {
    "code": 4210,
    "msg": "Expression correlated (select round(avg((str_to_duration((`completed_requests`.`serviceTime`)) / 1000000)), 1) from `#system`:`completed_requests` where ((`completed_requests`.`statement`) = (`cr`.`statement`))) must depend only on group keys or aggregates.",
    "query": "SELECT statement,\n       COUNT(*) AS count,\n       (\n           SELECT ROUND(AVG(STR_TO_DURATION(serviceTime) / 1000000), 1)\n           FROM system:completed_requests\n           WHERE statement = cr.statement) AS avg_service_time_ms\nFROM system:completed_requests cr\nGROUP BY statement\nORDER BY count DESC\nLIMIT 3"
  }
]

To be honest I don’t understand the error message. What am I doing wrong?

2

Subquery is correlated because it references parent query cr.statement
Pre 7.0.0 correlated queries requires USE KEYS. This has been fixed in upcoming 7.0.0

Your query is not required sub query.

SELECT statement,
       COUNT(1) AS count,
       ROUND(AVG(STR_TO_DURATION(cr.serviceTime) / 1000000), 1) AS avg_service_time_ms
FROM system:completed_requests cr
GROUP BY statement
ORDER BY count DESC
LIMIT 3
3

vsr1, perfect answer.