single column data [message #343256] |
Tue, 26 August 2008 15:22  |
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 #343260 is a reply to message #343259] |
Tue, 26 August 2008 15:27   |
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 #343263 is a reply to message #343261] |
Tue, 26 August 2008 15:37   |
kimber
Messages: 10 Registered: August 2008 Location: USA
|
Junior Member |
|
|
Should i use "IS NULL" and "SI NOT NULL" instead?
Thanksanacedent 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   |
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   |
pablolee
Messages: 2882 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   |
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   |
kimber
Messages: 10 Registered: August 2008 Location: USA
|
Junior Member |
|
|
Thank you all very much, i appreciate you all guru's time , thanksprtz 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   |
Frank
Messages: 7901 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   |
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 doSELECT count(column) FROM table;
|
|
|
Re: single column data [message #343387 is a reply to message #343385] |
Wed, 27 August 2008 02:45   |
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 doSELECT 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   |
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
|
|
|
|