CODEWITHSUNDEEP
oracle-databaseplsql
Marcus Leon
Marcus Leon

Reputation: 56679

Oracle count(*) returns different value for aggregate functions

Can anyone explain why the same query returns a different count depending on if an aggregate function is used?

--Returns 1
select count(*) from
(
    select min(date_field) from mytable where field1 = 'xxx'
)

--Returns 0
select count(*) from
(
    select * from mytable where field1 = 'xxx'
)

Upvotes: 1

Views: 62

Answers (1)

axiac
axiac

Reputation: 72226

In the absence of a GROUP BY clause, the query:

select min(date_field) from mytable where field1 = 'xxx'

always returns exactly one row, no matter how many rows of the table match the WHERE clause.

The lack of the GROUP BY clause forces the engine to create only one group from the rows filtered by the WHERE clause, therefore it returns one row. When there are no rows in the group, the expression min(date_field) evaluates to NULL.

On the other hand, the query:

select * from mytable where field1 = 'xxx'

produces zero or more rows (the rows that match the WHERE clause).

Upvotes: 4

Related Questions