Home » SQL & PL/SQL » SQL & PL/SQL » count for null year (oracle9.2.0.3)
count for null year [message #444786] Wed, 24 February 2010 03:41 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
with test as
( select 0001 item_code, to_date(1997,'yyyy') Yr from dual union all
select 0002 , to_date(1998,'yyyy') from dual union all
select 0003 , to_date(1999,'yyyy')  from dual union all
select 0004 , null from dual union all
select 0005 , null from dual)
select count(Yr) from test group by yr


COUNT(YR)

1
1
1
0

But I want the completed year count and non completed year count
The non completed year count should be 2 as there are two null
How I would acheive?


==============
Re: count for null year [message #444789 is a reply to message #444786] Wed, 24 February 2010 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
count(column) counts non null values in the column. Use nvl.
Re: count for null year [message #444794 is a reply to message #444786] Wed, 24 February 2010 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with test as
  2  ( select 0001 item_code, to_date(1997,'yyyy') Yr from dual union all
  3  select 0002 , to_date(1998,'yyyy') from dual union all
  4  select 0003 , to_date(1999,'yyyy')  from dual union all
  5  select 0004 , null from dual union all
  6  select 0005 , null from dual)
  7  select count(nvl(yr,to_date(1,'yyyy'))) from test group by yr
  8  /
COUNT(NVL(YR,TO_DATE(1,'YYYY')))
--------------------------------
                               2
                               1
                               1
                               1

SQL> with test as
  2  ( select 0001 item_code, to_date(1997,'yyyy') Yr from dual union all
  3  select 0002 , to_date(1998,'yyyy') from dual union all
  4  select 0003 , to_date(1999,'yyyy')  from dual union all
  5  select 0004 , null from dual union all
  6  select 0005 , null from dual)
  7  select count(*) from test group by yr
  8  /
  COUNT(*)
----------
         2
         1
         1
         1

Regards
Michel

[Updated on: Wed, 24 February 2010 04:05]

Report message to a moderator

Re: count for null year [message #444818 is a reply to message #444794] Wed, 24 February 2010 05:54 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

But I want the completed year count and non completed year count

the combination of result will be confusing...
2
1
1
1
How one would understand 2 is for noncompleted one or completed one?
with test as
( select 0001 item_code, to_date(1997,'yyyy') Yr from dual union all
select 0002 , to_date(1998,'yyyy') from dual union all
select 0003 , to_date(1999,'yyyy')  from dual union all
select 0004 , null from dual union all
select 0005 , null from dual )
select COUNT(YR) completed,SUM(DECODE(NVL(TO_CHAR(YR),'Y'),'Y',1,0)) noncompleted from test group by yr 



Previous Topic: Avoid & in plsql
Next Topic: Constraint Issue (merged)
Goto Forum:
  


Current Time: Sat Oct 01 14:27:36 CDT 2016

Total time taken to generate the page: 0.04093 seconds