How to grouby data in one column and distribute it in another column in HiveSQL?

I have the following data:

CompanyID	Department	No of People	Country
45390	HR	100	UK
45390	Service	250	UK
98712	Service	300	US
39284	Admin	142	Norway
85932	Admin	260	Germany

I wish to know how many people belong to the same department from different countries?

Required Output

Department	No of People	Country
HR	100	UK
Service	250	UK
	300	US
Admin	142	Norway
	260	Germany

I was able to get the data but the Department was repeated by this query.

""" select Department, Country,count(Department) from dataset
    group by Country,Department
    order by Department """

How can I get the desired output?

Answer

The result set that you are producing is not really a relational result set. Why? Because rows depend on what is in the “previous” row. And in a relational database, there is no such thing as a “previous” row. This type of processing is often handled in the application layer.

Of course, SQL can do what you want. You just need to be careful:

select (case when 1 = row_number() over (partition by Department order by Country) 
             then Department
        end) as Department,
       Country, count(*) as num_people,         
from dataset
group by Country,Department
order by Department, Country;

Note that the order by needs to match the window function clause to be sure that what row_number() considered to be the first row is really the first row in the result set.