GROUP BY Examples

In this post we will see few examples of using GROUP BY statement

CREATE TABLE  maths (
   student  string,
   category string, --'alg',  'trig'
   mark     string, --'10th', '11th'
   class    string
)                      
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

load data local inpath './a13_example.txt' 
overwrite into table maths;

hive> select * from maths;
alice alg 70 10
bob alg 72 10
alice trig 80 10
bob trig 81 10
john alg 90 11

Example 1

select 
   class 
from 
   maths 
group by class;

10
11

Example 2

select 
   class,
   sum(mark)
from 
   maths 
group by class;

10 303.0
11 90.0

Example 3

select 
   class, 
   category,
   case when category='alg' then sum(mark) else 'NA' end result
from 
   maths 
group by 
   class, category;

10 alg   142.0
10 trig  NA
11 alg   90.0