case_when

1.

select sname,score,
case 
when score>=20 and score<30 then 20
when score>=30 and score<40 then 30
when score<=40 and score<50 then 40
else 50
end as score_new from teacher

2.

drop table student;
create table if not exists student
(
name string,
orderdate string,
cost int,
sex string,
dep string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';



jack,2017-01-01,10,male,a
tony,2017-01-02,15,female,a
jack,2017-02-03,23,female,a
tony,2017-01-04,29,female,b
jack,2017-01-05,46,male,b


load  data local inpath "/home/kg/hive/student.txt" into table student;

 

select dep,
sum(case sex when "male" then 1 else 0 end) male_sum,
sum(case sex when "female" then 1 else 0 end) female_sum
from student
group by dep;


select dep,
sum(if( sex="male",1,0)) male_sum,
sum(if( sex="female",1,0)) female_sum
from student
group by dep;

a    1    2
b    1    1

 

select dep,sex,count(sex)from student group by dep,sex;
a    female    2
b    female    1
a    male    1
b    male    1