one query won't work, as totalcount is not in "group by".
You have 2 options:
1) use the sub query
select a.timestamp_dt, a.totalcount/b.total_sumfrom daily_count_per_kg_domain a
join(select timestamp_dt, sum(totalcount) as
total_sumfromdaily_count_per_kg_domaingroup by timestamp_dt) b on
(a.timestamp_dt = b.timestamp_dt)
2) If you are using hive 11 or above, using windows functions.
Yong
Date: Tue, 25 Feb 2014 18:27:34 -0600
Subject: Re: hive query to calculate percentage
From: kkrishna...@gmail.com
To: user@hive.apache.org
Modfiy the query to :select totalcount / sum(totalcount) from
daily_count_per_kg_domain where timestamp_dt = '20140219' group by timestamp_dt;
if you dont specify the where clause, you will get result for all partitions.
On Tue, Feb 25, 2014 at 3:14 PM, Manish <maa...@gmail.com> wrote:
I have a partitioned table on timestamp_dt:
> desc daily_count_per_kg_domain;
OK
ddnamesyskg string
totalcount int
timestamp_dt string
hive> select * from daily_count_per_kg_domain;
OK
sys_kg_band 224 20140219
sys_kg_event 3435 20140219
sys_kg_movies 44987 20140219
sys_kg_oly 4172 20140219
sys_kg_sp_countr 5499 20140219
sys_kg_sports 3954 20140219
sys_kg_tv 21387 20140219
sys_kg_venue 152 20140219
sys_kgathlete 9000 20140219
sys_kgpeople 300064 20140219
Looking to compute percentages for each row, (per day):
100* totalcount / sum(totalcount)
Intuitively i tried :
> select totalcount / sum(totalcount) from daily_count_per_kg_domain where
> timestamp_dt = '20140219' ;
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY
key 'totalcount'
I am not sure what group by on totalcount means..
Any ides ?
