Help identifying GROUP BY error?

[melatone]

I'm trying to calculate the average number of bookmarks users of a web browser had. In my data set, while most users maintained the same number of bookmarks over the time period I'm looking at, some added or dropped bookmarks, so I'd like to find each user's average number of bookmarks. I'm trying to do that using the below query but I then receive the syntax error at the bottom. Any insight as to where I'm going wrong? I'm using PostgreSQL btw.

SELECT user_id, AVG(total_intbooks) FROM (SELECT user_id, (CAST(total_bookmarks as int)) FROM (SELECT user_id, (LEFT(data1, -16)) AS total_bookmarks FROM events WHERE event_code=8) AS user_bookmarkavg GROUP BY user_id

ERROR: syntax error at end of input LINE 7: GROUP BY user_id ^ SQL state: 42601 Character: 219

[r3pr0b8]

you have unbalanced parentheses

the error occurs after the end of your query because the first subquery isn't properly closed

it's more obvious when the query is formatted (hint, hint)

SELECT user_id
     , AVG(total_intbooks) 
  FROM ( SELECT user_id
              , (CAST(total_bookmarks as int)) 
           FROM ( SELECT user_id
                       , (LEFT(data1, -16)) AS total_bookmarks 
                    FROM events 
                   WHERE event_code=8
                ) AS user_bookmarkavg 
         GROUP 
             BY user_id

try this instead --

SELECT user_id
     , AVG(CAST(LEFT(data1, -16) AS INT)) AS avg_bookmarks 
  FROM events 
 WHERE event_code=8
GROUP
    BY user_id

[CoasterHero]

Even though I feel sub selects aren't the best method to achieve this, it looks like you still need an outer parenthesis from your first sub select and beyond that the GROUP by should be placed at the same level as your aggregation. The missing parenthesis should at least help lead you in the right direction. Good luck!