CODEWITHSUNDEEP
sqlhadoophiveaggregate-functions
Previnkumar
Previnkumar

Reputation: 49

How to query a column created by aggregate function in hive?

In hive, I want to select the records with users>=40. My table column consist of field userid. So i used

select title,sum(rating),count(userid) from table_name where count(userid)>=40 group by title order by rating desc

But it showed error like you can't use count in where clause. Also i have tried using alias like

select title,sum(rating) as ratings,count(userid) as users where users>=40 group by title order by ratings desc

Here also i struck up with error showing users is not a column name in table. I need to get title with maximum ratings having minimum 40 users

Upvotes: 1

Views: 1422

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You want the having clause:

select title, sum(rating), count(userid) 
rom table_name
group by title
having count(userid) >= 40 
order by sum(rating) desc;

In Hive, you may need to use a column alias, though:

select title, sum(rating) as rating, count(userid) as cnt
rom table_name
group by title
having cnt >= 40 
order by rating desc;

Upvotes: 2

Related Questions