CODEWITHSUNDEEP
group-byoracle12cstored-functions
Robert Kock
Robert Kock

Reputation: 6028

SELECT: Group by function result

I have a stored function that calculates the status of an entity (this status is NOT an attribute of the entity but needs to be calculated).
Within a SELECT statement, I want to know how many elements have which status.

For now, I got the following:

SELECT   my_function(entity_id) AS status,
         COUNT(*)
FROM     some_table
GROUP BY my_function(entity_id) -- entity_id is a column of some_table
ORDER BY status;

This works but I find it quite ugly to repeat the function call within the GROUP BY statement.
The code above is just an example. The actual function is defined within a package and has several input parameters.

Is it possible to do this without refering to the stored function within the GROUP BY statement?

Upvotes: 1

Views: 44

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You can wrap your query in an external one to isolate the function call and use an alias:

select count(*),
       status
from ( 
        select s.*,
               my_function(entity_id) as status
        from some_Table s
     )
group by status
order by status

Upvotes: 2

Related Questions