CODEWITHSUNDEEP
oracle-databaseselectgroup-by
istovatis
istovatis

Reputation: 1408

Oracle: Selecting using inner select

I have the following example:

Select MAX(1) as foo, count(*) as bar From dual

This is executed properly by returning 1 as foo and 1 as bar.

Now I will create an inner select that returns only the foo, retaining the bar as it was in previous example.

Select (Select Max(1) as foo From dual), count(*) as bar From dual

The latter example will give the following error:

ORA-00937: not a single-group group function

This can be easily fixed by providing a (redundant?) Group By:

 Select (Select Max(1) From dual) as foo, count(*) as bar From dual Group By 1

My question is the following: Since we certainly return a single row in inner select (by providing the MAX aggregated function) why is necessary to declare a group by at the end of the statement? Also, will this group by addition have an effect in statement's performance?

Upvotes: 3

Views: 123

Answers (2)

Stawros
Stawros

Reputation: 935

You can use over directive in count() - 

Select (Select Max(1) as foo From dual) as foo, count(*) over() as bar
  From dual

Upvotes: 0

Mihail
Mihail

Reputation: 151

In the first case

Select MAX(1) as foo, count(*) as bar From dual

you do not need a group by expression, because you have two aggregate functions and no other values.

In the second case

Select (Select Max(1) From dual) as foo, count(*) as bar From dual Group By 1

suddenly one of the selected values is not an aggregate function but some kind of another value - a subquery result set in particular - and Oracle can't know how many values that result set will have. So you need to have a group by on it.

The performance impact will not be caused by the group by per se, but by the subquery. In the first example you will have only one scan of DUAL, and in the second - you will have TWO table scans - one for the subquery and one for the main query. You can see that in the execution plans.

Upvotes: 1

Related Questions