Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Count (*) query is returning null?

BS2012Apr 21 2014 — edited Apr 21 2014

Hi Everyone,

My DB version is

BANNER                                                        

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production                          

CORE 10.2.0.1.0 Production                                        

TNS for Linux: Version 10.2.0.1.0 - Production                  

NLSRTL Version 10.2.0.1.0 - Production        

Please do have a look at these below queries and clear my doubts.

Below I've written a count query on the top of analytical function. When condition matches, then it's returning 1 but when condition fails, I'm expecting '0' in return but the query returns null. Why?

Count query will always give you some value like 0, 1 ,2 3 etc. Please suggest me something.

SELECT COUNT(*) OVER (PARTITION BY ENAME, SAL) REC_COUNT

FROM EMP

WHERE ENAME = 'SMITH'

AND SAL     = '800'; --This query will give the count as 1

SELECT COUNT(*) OVER (PARTITION BY ENAME, SAL) REC_COUNT

FROM EMP

WHERE ENAME = 'ABABABAB'

AND SAL     = '800'; --This query will return null as ''ABABABAB' is not there in emp table.

--But this should return 0, please correct me if I'm wrong.

Regards,

BS2012.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 19 2014
Added on Apr 21 2014
9 comments
3,784 views