Home » SQL & PL/SQL » SQL & PL/SQL » Count Number of Null Column Values of each Record in a Table
Count Number of Null Column Values of each Record in a Table [message #305250] Mon, 10 March 2008 04:20 Go to next message
hasnainlakhani
Messages: 24
Registered: January 2007
Junior Member
Is it possible to count number of null column values of each record in a Table. if yes then what will be the query

Re: Count Number of Null Column Values of each Record in a Table [message #305252 is a reply to message #305250] Mon, 10 March 2008 04:23 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
select count(*) from tab1 where col1 IS NULL;
Re: Count Number of Null Column Values of each Record in a Table [message #305256 is a reply to message #305252] Mon, 10 March 2008 04:30 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Or, how about this:

SQL> CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER);

Table created.

SQL> INSERT INTO t1 VALUES (1, 2, NULL);

1 row created.

SQL> INSERT INTO t1 VALUES (3, NULL, NULL);

1 row created.

SQL> INSERT INTO t1 VALUES (NULL, NULL, NULL);

1 row created.

SQL> SELECT nvl2(c1, 0, 1) + nvl2(c2, 0, 1) + nvl2(c3, 0, 1) FROM t1;

NVL2(C1,0,1)+NVL2(C2,0,1)+NVL2(C3,0,1)
--------------------------------------
                                     1
                                     2
                                     3
Re: Count Number of Null Column Values of each Record in a Table [message #305327 is a reply to message #305250] Mon, 10 March 2008 07:58 Go to previous messageGo to next message
hasnainlakhani
Messages: 24
Registered: January 2007
Junior Member
I dont want to mention where clause that

select count(*) from tab1 where col1 IS NULL;

I want to find out row by row in paricular table that how many fields are null without mentioning column name in where clause.

For example Row1 contains 5 null values
Row2 contains 3 null values
Row 3 contains 2 null values and so on

Secondly I want to extract those rows from table which contains at least 1 null column value without mentioning each and every column name in query i-e- col1 is null or col2 is null etc.
Just I will give Table name as a Parameter and it will extract all those records which have atleast 1 null column values.



Re: Count Number of Null Column Values of each Record in a Table [message #305333 is a reply to message #305327] Mon, 10 March 2008 08:15 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Yes, and I want a US$1,000,000 for my next birthday. However, neither of us is going to get what we want Smile
Re: Count Number of Null Column Values of each Record in a Table [message #305338 is a reply to message #305327] Mon, 10 March 2008 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can do it if you can answer my following question:
How many rooms are empty in my building.
You have to answer WITHOUT looking in the rooms.

Regards
Michel
Re: Count Number of Null Column Values of each Record in a Table [message #305340 is a reply to message #305338] Mon, 10 March 2008 08:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
good old Schrödinger...

Until you actually check the columns, the columns are null AND have a value.

[Updated on: Mon, 10 March 2008 08:32]

Report message to a moderator

Re: Count Number of Null Column Values of each Record in a Table [message #305345 is a reply to message #305340] Mon, 10 March 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
good old Schrödinger...
Until you actually check the columns, the columns are null AND have a value.

Laughing

Regards
Michel
Re: Count Number of Null Column Values of each Record in a Table [message #305372 is a reply to message #305340] Mon, 10 March 2008 09:47 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
./fa/449/0/
Re: Count Number of Null Column Values of each Record in a Table [message #305496 is a reply to message #305250] Tue, 11 March 2008 01:15 Go to previous messageGo to next message
hasnainlakhani
Messages: 24
Registered: January 2007
Junior Member
Hello Guys

i think you are making Software for Buildings Lols.


Well let u tell one time again..

Did you ever use Dictionary

USER_TAB_COLUMN Table or Select * from cols.

if u describe the table u will get the column of Number of Nulls and number of distinct. Can u tell me what the Oracle want to show by this column.

and basically my friend i want a simple think that....

I just want to found that im my entire users or table, how many columns vales are null.

and i just researched and found that USER_TAB_COLUMN will
give me but not giving me correct.

By the Way Frank tell me when your next birthday coming. i will send you a gift Wink

Have a nice day guys

Thanks Smile
Re: Count Number of Null Column Values of each Record in a Table [message #305505 is a reply to message #305496] Tue, 11 March 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who is "u"? He seems to do a lot of work.

Quote:
if u describe the table u will get the column of Number of Nulls and number of distinct.

Wrong, if you describe (use the "describe" command) you will get if a column may be null or not.

Quote:
i just researched and found that USER_TAB_COLUMN will
give me but not giving me correct

It only gives a computation or estimation from the last statistics gathering.

Regards
Michel

[Updated on: Tue, 11 March 2008 01:46]

Report message to a moderator

Re: Count Number of Null Column Values of each Record in a Table [message #389673 is a reply to message #305250] Tue, 03 March 2009 02:32 Go to previous message
rrathi
Messages: 1
Registered: March 2009
Junior Member
Hi,

You can use the following query

Select sum(nvl2(col1,0,1)) from table_name;


Cheers,
Rahul.
Previous Topic: Problem on multi value parameter with IN operator
Next Topic: Insert into confusion
Goto Forum:
  


Current Time: Sat Dec 10 04:42:32 CST 2016

Total time taken to generate the page: 0.13737 seconds