Analytics Software

Can sql aggregate functions be added to an interface select clause and from clause.

I want to have a datasource where one of the columns is an aggregate such as a count, which would need the ability to indicate a from clause function group by, or having by. In the Designer Diagram tab for the interface, it seems you can only apply sql functions to where clause joins or filters.

I want to have a query such as follows as my datasource

select columnA, count(*) as thecount
from table x
group by columnA
order by thecount asc

and ultimately

select columnA, thecount
from (select columnA, count(*) as thecount
from table x
group by columnA
order by thecount asc)
where rownum <1001

Can this be done in the ODI Designer, without creating a physical custom view on the database?