Counting null/blank/data columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting null/blank/data columns

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2010-10-04 : 06:58:46

Good Morning/Afternoon

Hello all,

I'm try to tidy up a CRM table in a DB.

The issue i have is there is a large table some of the columns have a null value, blank value or the correct data filled in.

What i would like to do write a query that will count the three diffent values(null/blank/data ) so i can tell where a majority of the data is missing.

SELECT Company,

sUserName ,
sUserPassword ,
sDepartment ,
City ,
Country ,
Email ,
JobTitle ,
Address ,
PostCode ,
FirstName ,
LastName ,
JobFunction ,
Industry ,
County ,
Telephone
FROM PD2

Any help would be great.

Thanks Lee

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-04 : 07:04:19

SELECT CASE WHEN Company IS NULL THEN 1 ELSE 0 END Company_Null,
CASE WHEN Company='' THEN 1 ELSE 0 END Company_Blank,
CASE WHEN Company<>'' THEN 1 ELSE 0 END Company_Data,
...
FROM PD2

Sachin.Nand

2937 Posts

Posted - 2010-10-04 : 07:09:51

quote:
Originally posted by robvolk

SELECT
SUM(CASE WHEN Company IS NULL THEN 1 ELSE 0 END) Company_Null,
SUM(CASE WHEN Company='' THEN 1 ELSE 0 END) Company_Blank,
SUM(CASE WHEN Company<>'' THEN 1 ELSE 0 END) Company_Data,
...
FROM PD2

Little correction in red.

Edit : replaced count case with sum case

PBUH

lemondash
Posting Yak Master

159 Posts

Posted - 2010-10-04 : 07:14:06

Ah ok thanks that points me in the right direction.

I would assume that i need to sum that to get a total value ? i.e. sum(CASE WHEN Company IS NULL THEN 1 ELSE 0) END ASCompany_Null

Sachin.Nand

2937 Posts

Posted - 2010-10-04 : 07:15:05

quote:
Originally posted by lemondash

Ah ok thanks that points me in the right direction.

I would assume that i need to sum that to get a total value ? i.e. sum(CASE WHEN Company IS NULL THEN 1 ELSE 0) END ASCompany_Null

Oops sorry it should had been rather sum case & not count case.

PBUH

lemondash
Posting Yak Master

159 Posts

Posted - 2010-10-04 : 07:22:54

hmm for some strange reason teh results are are populating the same value for all three fields.

my ouput for
lCompanyId Department NULL Department Blank Department Data
3976 7 7 7

lemondash
Posting Yak Master

159 Posts

Posted - 2010-10-04 : 07:25:48

Cheers guys that works with the sum. Being really cheeky here but is there any way i can put the output in the colunm.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-04 : 07:31:11

What do you mean when you say "put the output in the column"?

No, you're never too old to Yak'n'Roll if you're too young to die.

lemondash
Posting Yak Master

159 Posts

Posted - 2010-10-04 : 07:39:31

so i can see the results like this
Department
Null
Blank
Data

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-04 : 08:00:18

quote:
Little correction in red.

Thank you, hadn't had coffee yet.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-04 : 08:08:36

select
case when Company is null then null
when Company = '' then 'Blank'
else 'Data'
end as Company,
count(*) as counter
from PD2
group by
case when Company is null then null
when Company = '' then 'Blank'
else 'Data'
end

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources