CODEWITHSUNDEEP
sqlhive
zhihao_li
zhihao_li

Reputation: 183

get aggregate meatures from a subquery

Given "my_table":

id       other_columns
a        ...
a        ...
b        ...
b        ...
b        ...

I am trying to get mean, min, max of line count across different IDs. So as a Hive learner, I was trying this:

SELECT avg(line_count), min(line_count), max(line_count)
FROM (SELECT count(*) AS line_count FROM my_table GROUP BY id);

Could someone explain the associated error message of 

"cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in subquery source"?

Thanks a lot!

Upvotes: 0

Views: 27

Answers (1)

rohitvats
rohitvats

Reputation: 1851

Try giving an alias to the subquery:

SELECT avg(s.line_count), min(s.line_count), max(s.line_count)
FROM (SELECT count(*) AS line_count 
     FROM my_table GROUP BY id) AS s;

Upvotes: 1

Related Questions