How to fix hive code for counting a column and group by another column?

Question

There are 3 columns in my hive data (user, gender, rating). now, I want to count number of user_id, gender wise. I have written hive code as

select user_id, gender, count(*) from u_user group by user_id;

but the error that I have got is

SemanticException [Error 10025]: Line 1:16 Expression not in GROUP BY key 'gender'

How to fix this?

Answer 1

Well, the keys you group by should be the same with the keys in the select. As below:

select user_id,gender,count(1) from u_user group by user_id,gender;

And if you want to count user_id of each gender type , you can write like this:

select gender,count(distinct user_id) from u_user group by gender;