Beehive: UDF and GROUP BY

Question

I have a UDF (GetUrlExt) that returns an extension. (e.g. jpg in /abc/models/xyz/images/top.jpg). Data as below:

Date Time TimeTaken uristem 9/5/2011 0:00:10 234 /abc/models/xyz/images/top.jpg 9/5/2011 0:00:11 456 /abc/models/xyz/images/bottom.jpg 9/5/2011 0:00:14 789 /abc/models/xyz/images/left.gif 9/5/2011 0:00:16 234 /abc/models/xyz/images/top.pdf 9/5/2011 0:00:18 734 /abc/models/xyz/images/top.pdf 9/5/2011 0:00:19 654 /abc/models/xyz/images/right.gif 9/5/2011 0:00:21 346 /abc/models/xyz/images/top.pdf 9/5/2011 0:00:24 556 /abc/models/xyz/images/front.pdf 9/5/2011 0:00:26 134 /abc/models/xyz/images/back.jpg 

A query without "GROUP BY" works fine:

 SELECT GetUrlExt(uristem) AS extn FROM LogTable; 

Result: JPG JPG GIF PDF PDF GIF PDF PDF JPG

Now I need "GROUP BY" according to the results of GetUrlExt UDF.
Expected Result:
jpg 3 274.6
gif 2 721.5
pdf 4 467.5

But the following query does not work:

 SELECT GetUrlExt(uristem) AS extn, Count(*) AS PerCount, Avg(TimeTaken) AS AvgTime FROM LogTable GROUP BY extn; 

Any help is appreciated!

Answer 1

Pls uses a subquery to group by.

The bush does not support the group by the calculated value directly.

 SELECT a.extn, Count(*) AS PerCount, Avg(TimeTaken) AS AvgTime FROM ( SELECT GetUrlExt(uristem) AS extn, TimeTaken FROM LogTable ) a GROUP BY a.extn; 

Answer 2

You can either include the group by an alias, or you need the entire statement in the group

 SELECT GetUrlExt(uristem) AS extn, Count(*) AS PerCount, Avg(TimeTaken) AS AvgTime FROM LogTable GROUP BY GetUrlExt(uristem);