Home » SQL & PL/SQL » SQL & PL/SQL » Need Hlp on Sql
icon5.gif  Need Hlp on Sql [message #363303] Fri, 05 December 2008 14:51 Go to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
Hi All,

I am New to this forum...I got a question for u guys?...

I have a column called 'lastname' It allows 'Nulls'

Assume that column has 2000 rows

Out of tht 2000 rows----1700 are nulls

and remaining 300 rows has the date tht means which is not null

I need the Percentage of the NotNulls

300/2000*100 = 15.0%

I need the Query tht shows it's 15%

It should be work in Oracle Sql Plus Not Sql Server

Can anybody Help me On tht Plz........

Re: Need Hlp on Sql [message #363305 is a reply to message #363303] Fri, 05 December 2008 14:58 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
It should be work in Oracle Sql Plus Not Sql Server
I'm not sure I can produce such a query. What if it, by some chance, works on MS SQL Server?

By the way, how does YOUR query look like? Show use some of your attempts!
Re: Need Hlp on Sql [message #363309 is a reply to message #363303] Fri, 05 December 2008 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use ratio_to_report function, it does not exist in MS SQL Server.

Regards
Michel
Re: Need Hlp on Sql [message #363311 is a reply to message #363303] Fri, 05 December 2008 15:18 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
Hi I worked this before In Sql Server

select (cast((Count(*)-(select count(*) from Table_name where isnull(column_Name,'')=''))as

float(2,2))*100)/ count(*) as IncompletePercent from Table_Name;


If we use isnull it picks Both Nulls and Empty Rows too

But the same Query is not working in Oracle Sql Plus

if Know...How to work this can You please help
Re: Need Hlp on Sql [message #363312 is a reply to message #363311] Fri, 05 December 2008 15:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try the link I posted.

Also Post a test case: create table and insert statements along with the result you want with these data.

And read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).


Regards
Michel

[Updated on: Fri, 05 December 2008 15:21]

Report message to a moderator

Re: Need Hlp on Sql [message #363313 is a reply to message #363311] Fri, 05 December 2008 15:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also make it simple with "count(column)/count(*)" but the problem is that it also works in MS SQL Server.

Regards
Michel
Re: Need Hlp on Sql [message #363314 is a reply to message #363303] Fri, 05 December 2008 15:38 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
Oracle 10.2.0

Table Name :- Persons
Column Name :- LastName

Total Count
------------

Select count(Lastname) from Persons;

2000 Rows Selected

Nulls Count
-----------

Select Count(Lastname) from persons where lastname is Null;

1700 Rows Selected

Not Nulls Count
---------------

Select Count(Lastname) from Persons where lastname is not NUll;

300 Rows Selected

Not Null Percentage
------------------

???????????????????????? ( 300/2000 * 100 = 15%)

That wht i need....

Great Helpfull for me if you give the Answer

Thanks

Re: Need Hlp on Sql [message #363320 is a reply to message #363303] Fri, 05 December 2008 16:41 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
I Worked on it...I got it

Thx for the try Buddies...

People Must be Polite in this Forums ..

If You guys didn't Understand the Question ask it Agiain We will try to Explain it Better

Don't laugh on others Doubts....do remember tht You guys came from the Juniour level itself....

Anyways I got the Answer thx a lot for your tries

Thanks
Re: Need Hlp on Sql [message #363326 is a reply to message #363320] Fri, 05 December 2008 18:02 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
malladipavan wrote on Sat, 06 December 2008 04:11
I Worked on it...I got it



Can you post your solution? I for one am curious for the SQL you have used for this issue in Oracle which doesn't work in MS SQL Server. Also it might be helpful for people who might be facing similar "problems" and looking for solutions in this forum.


Regards,
Jo

[Updated on: Fri, 05 December 2008 18:03]

Report message to a moderator

Re: Need Hlp on Sql [message #363354 is a reply to message #363314] Sat, 06 December 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oracle 10.2.0

Table Name :- Persons
Column Name :- LastName

Total Count
------------

Select count(Lastname) from Persons;

2000 Rows Selected

Nulls Count
-----------

Select Count(Lastname) from persons where lastname is Null;

1700 Rows Selected

This is plainly wrong.
If you want help, DON'T LIE.

SQL> insert into t (id) values ('A');

1 row created.

SQL> insert into t (id) values (null);

1 row created.

SQL> select count(*) from t;
  COUNT(*)
----------
         2

1 row selected.

SQL> select count(id) from t;
 COUNT(ID)
----------
         1

1 row selected.

COUNT(col) does not count null values.
You faked your output.
NEVER do that or we will not trust you and what you will post.

In addition, read forum guide as requested and format your post and don't use IM speak.

Regards
Michel


Re: Need Hlp on Sql [message #364476 is a reply to message #363303] Mon, 08 December 2008 15:49 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
I am Srry abt tht....

Actually i didn't see when i am typing

To find Nulls it's

Select Count(*) from persons where lastname is Null;

srry abt tht ... my intention is not to mislead the people

i didn;t check the query when i am typing....

anyways i got the query to find the Notnull percentage in Oracle

Here it is

Tble Name : Person

Column Name : Lastname

1)select count(*) from person;

2000 rows selected

To Find Nulls;
--------------
2)select count(*) from person where lastname is Null;

1700

To Find Not Nulls;
-------------------
3) select count(*) from Person where lastname is not Null;

or

select count(lastname) from person;

(Both will work to find NotNulls in Oracle)

Not Null Percentage;
--------------------
4) select (cast((count(lastname)) as float) * 100)/count(*) as Notnullpercentage from person;


Note:- In oracle when we count a column..It counts only Notnull values where sqlserver is not like that


Thanks



Re: Need Hlp on Sql [message #364537 is a reply to message #364476] Tue, 09 December 2008 00:38 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am Srry abt tht....

You still didn't read and follow the guidelines.

Regards
Michel
Previous Topic: Merge
Next Topic: Views underlying table on separate machine
Goto Forum:
  


Current Time: Wed Dec 07 04:59:12 CST 2016

Total time taken to generate the page: 0.11504 seconds