I have a table with the schema as shown below:
CREATE TABLE Table1 (membertype varchar(1));
INSERT INTO Table1 (membertype) VALUES ('Y'),('H'),('U'),('W'),('W'),('W'),('H'),('H'),('U'), ('U'),('P'),('P'),(''),('P'),('P'),('P'),(''),('W'), ('Y'),('Y'),('Y'),('H'),('D'),('D'),('D'),('D'),('H'), ('W'),('W');
What I am trying to achieve is to get the count of each type in the column and also their percentage to the total number of types in the column.
I tested the below query in MYSQL and got the desired result:
select (case when m.membertype='Y' then 'Young Adult' when m.membertype='H' then 'Head' when m.membertype='W' then 'Spouse' when m.membertype='P' then 'Aged Parent' when m.membertype='U' then 'Unknown' when m.membertype='D' then 'Deceased' else 'No Match' end) as type,count() as typecount,count()/t.total*100 as percentage from Table1 as m, (select count(*) as total from Table1) as t group by membertype;
type typecount percentage No Match 2 6.8966 Deceased 4 13.7931 Head 5 17.2414 Aged Parent 5 17.2414 Unknown 3 10.3448 Spouse 6 20.6897 Young Adult 4 13.7931
**
** http://sqlfiddle.com#!9/377d0/2
The same query fails in HIVE with the below error:
select (case when h.member='Y' then 'Young Adult' when h.member='H' then 'Head' when h.member='W' then 'Spouse' when h.member='P' then 'Aged Parent' when h.member='U' then 'Unknown' when h.member='D' then 'Deceased' else 'No Match' end) as type,count() as typecount,count()/t.total*100 as percentage from hhinfo as h,(select count(*) as total from hhinfo) as t group by member;
FAILED: SemanticException [Error 10025]: Line 1:277 Expression not in GROUP BY key 'total'
What am I missing here? Any help would be appreciated.