Home » SQL & PL/SQL » SQL & PL/SQL » single column data (oracle 10.1.2)
single column data [message #343256] Tue, 26 August 2008 15:22 Go to next message
kimber
Messages: 10
Registered: August 2008
Location: USA
Junior Member
Dear Guru's
I have a column, which can have NULL values, how can i find the total number of records and subtract the ones with null values in it?
thanks
Re: single column data [message #343257 is a reply to message #343256] Tue, 26 August 2008 15:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
By counting all of the rows that are not null
Re: single column data [message #343259 is a reply to message #343256] Tue, 26 August 2008 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
count where col is not null

Regards
Michel
Re: single column data [message #343260 is a reply to message #343259] Tue, 26 August 2008 15:27 Go to previous messageGo to next message
kimber
Messages: 10
Registered: August 2008
Location: USA
Junior Member
Do i have to use :

SUM(CASE WHEN(COUNT(FIELD)=NULL)) MINUS SUM(CASE WHEN(COUNT(FILED1)!=NULL))
Michel Cadot wrote on Tue, 26 August 2008 15:24
count where col is not null

Regards
Michel


Re: single column data [message #343261 is a reply to message #343256] Tue, 26 August 2008 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Never, ever use "=" or "!=" with NULL
Re: single column data [message #343263 is a reply to message #343261] Tue, 26 August 2008 15:37 Go to previous messageGo to next message
kimber
Messages: 10
Registered: August 2008
Location: USA
Junior Member
Should i use "IS NULL" and "SI NOT NULL" instead?

Thanks
anacedent wrote on Tue, 26 August 2008 15:30
Never, ever use "=" or "!=" with NULL

Re: single column data [message #343273 is a reply to message #343256] Tue, 26 August 2008 16:33 Go to previous messageGo to next message
prtz
Messages: 11
Registered: January 2008
Junior Member
Quote:

I have a column, which can have NULL values, how can i find the total number of records and subtract the ones with null values in it?



test@XE> --
test@XE> drop table t;

Table dropped.

test@XE> create table t (x varchar2(10));

Table created.

test@XE>
test@XE> --
test@XE> -- distribute NULLs somewhat randomly
test@XE> --
test@XE> insert into t (x)
  2  select case when substr(str,1,1) > 'M' then null else str end
  3  from (select dbms_random.string(1,10) as str
  4          from all_objects
  5         where rownum < 11);

10 rows created.

test@XE> commit;

Commit complete.

test@XE> select * from t;

X
----------




FIMRJJZWSC


FVOPREIWBN



10 rows selected.

test@XE>
test@XE> --
test@XE> select
  2    count(*) as total_records,
  3    sum(case when x is null then 1 end) as null_records,
  4    sum(case when x is not null then 1 end) as non_null_records
  5  from t;

TOTAL_RECORDS NULL_RECORDS NON_NULL_RECORDS
------------- ------------ ----------------
           10            8                2

test@XE>
test@XE> -- ==============================================================
test@XE> -- once more
test@XE> truncate table t;

Table truncated.

test@XE>
test@XE> --
test@XE> -- distribute NULLs somewhat randomly
test@XE> --
test@XE> insert into t (x)
  2  select case when substr(str,1,1) > 'M' then null else str end
  3  from (select dbms_random.string(1,10) as str
  4          from all_objects
  5         where rownum < 11);

10 rows created.

test@XE> commit;

Commit complete.

test@XE> select * from t;

X
----------
MESHRPEUKO


CVBUULCNRQ

EEGFAOQKDU
JHPMIQDNNN
LXXSITKQAM

FLUHCXAAOK

10 rows selected.

test@XE>
test@XE> --
test@XE> select
  2    count(*) as total_records,
  3    sum(case when x is null then 1 end) as null_records,
  4    sum(case when x is not null then 1 end) as non_null_records
  5  from t;

TOTAL_RECORDS NULL_RECORDS NON_NULL_RECORDS
------------- ------------ ----------------
           10            4                6

test@XE>
test@XE>


HTH
prtz

[Updated on: Tue, 26 August 2008 16:33]

Report message to a moderator

Re: single column data [message #343276 is a reply to message #343273] Tue, 26 August 2008 17:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
prtz, are you trying to say that you would use this method:
sum(case when x is not null then 1 end)
to count the number of non-null rows in a column? Why?
Re: single column data [message #343280 is a reply to message #343276] Tue, 26 August 2008 17:56 Go to previous messageGo to next message
prtz
Messages: 11
Registered: January 2008
Junior Member
Oops, sorry - my testcase gave the wrong impression.

Your suggestion is appropriate for count of non-null values in column x:

select count(x)
from t
where x is not null;


For count of null values in column x:

select count(x)
from t
where x is null;


Only in the (highly unlikely) case when all the 3 counts (total, non-null and null) are to be determined, would one go for the query in my earlier post.

prtz
Re: single column data [message #343298 is a reply to message #343280] Tue, 26 August 2008 19:44 Go to previous messageGo to next message
kimber
Messages: 10
Registered: August 2008
Location: USA
Junior Member
Thank you all very much, i appreciate you all guru's time , thanks
prtz wrote on Tue, 26 August 2008 17:56
Oops, sorry - my testcase gave the wrong impression.

Your suggestion is appropriate for count of non-null values in column x:

select count(x)
from t
where x is not null;


For count of null values in column x:

select count(x)
from t
where x is null;


Only in the (highly unlikely) case when all the 3 counts (total, non-null and null) are to be determined, would one go for the query in my earlier post.

prtz


Re: single column data [message #343372 is a reply to message #343263] Wed, 27 August 2008 01:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
kimber wrote on Tue, 26 August 2008 22:37
Should i use "IS NULL" and "SI NOT NULL" instead?

Thanks


Why bother to even post this and wait for answers when it is SO easy to just test it yourself?!
Re: single column data [message #343385 is a reply to message #343372] Wed, 27 August 2008 02:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, as COUNT does not count null values, to get the number of non-null values, just do
SELECT count(column) FROM table;
Re: single column data [message #343387 is a reply to message #343385] Wed, 27 August 2008 02:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Wed, 27 August 2008 02:36
Or, as COUNT does not count null values, to get the number of non-null values, just do
SELECT count(column) FROM table;








count(column)-- Excludes null

count(*) - Includes null values






Regards,
Oli

[Updated on: Wed, 27 August 2008 02:51]

Report message to a moderator

Re: single column data [message #343389 is a reply to message #343387] Wed, 27 August 2008 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Count(*) cannot be said to be counting null value columns, because * is not a column in any table.
Count(*) returns the count of all rows, rather than the count of the number of times a given expression is not null
Re: single column data [message #343394 is a reply to message #343389] Wed, 27 August 2008 03:17 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@Jrowbottom: Thanks for the update.




Regards,
Oli
Previous Topic: address format
Next Topic: procedures using cursors
Goto Forum:
  


Current Time: Sun Dec 04 18:54:30 CST 2016

Total time taken to generate the page: 0.10841 seconds