SQL: Average of counts
<p>In my Hive table, I have the following columns:</p>

<pre><code>date, SessionID, url,              time_spent
20160901 119     www.hello.com/hehe  10
20160901 121     www.abc.com/xyz     5
20160901 119     www.google.com      12
</code></pre>

<p>I need to find the number of urls per sessionid for all dates.
I tried something on the lines of</p>

<pre><code>Select sessionid, count(url), sum(time_spent) from my_table where date between '20160901' and '20160905' group by sessionid
</code></pre>

<p>... which does work for a tiny timeframe but there are millions of sessionid's per day and I need to get a year's worth of data, so this approach wouldn't work.</p>

<p>I figured an average representation of counts would work so I tried this:</p>

<pre><code>select count(sessionid), average(count(url)), avg(sum(time_spent)) from my_table where date between '20160901' and '20160905' group by sessionid;
</code></pre>

<p>But this fails with </p>

<blockquote>
  <p>Not yet supported place for UDAF 'count'</p>
</blockquote>

<p>Is there a way to get the average of counts here? </p>