Count of NON-NULL fields across

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

Count of NON-NULL fields across

Author

Topic

nj8988
Starting Member

3 Posts

Posted - 2013-04-26 : 11:14:29

I usually work in Excel, but need to write a query to count how many records have 1, 2, 3 or 4 phone numbers. I will be selecting the 4 fields from a table, but would like SQL to count how many records had 1 phone number (or 3 null fields), and how many had 2 phone numbers (or 2 null fields). The data will look like this;

Row 1: 6142920211,7048879877,2165874511,3302389299
Row 2: 3307994766,,,
Row 3: 2165749877,,7987451122
Row 4: ,2168745544,,

Row 1 would return 4
Row 2 would return 1
Row 2 would return 2
Row 3 would return 1

Is this possible? All I need is the statement of how to get SQL to count the non-null fields (or null fields) in each row. Any help would be greatly appreciated.

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-04-26 : 11:30:13

Try this?

select
case when Phone1 is not null then
case when Phone2 IS not null then
case when Phone3 IS not null then
case when Phone4 IS not null then 4
else 3
end
else 2
end
else 1
end
else 0
end
from table_name

repalce phone1,2,3,4 with your columns
and table name also.

Thanks,

M.MURALI kRISHNA

satheesh
Posting Yak Master

152 Posts

Posted - 2013-04-26 : 11:45:01

Have a look and try some thing like this
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9fb882b0-854a-4124-8a92-2b70e272e1ae/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-26 : 12:04:48

@nj8988, it is much easier for us to help if you provide sample data in a consumable format. You are using some confusing terms like field and record. Since they don't exist in SQL Server, I'm not sure if you are referring to Excel, since you mention you are using that. I'm guessing that in the sample data you provided, the commas represent column separators, not just a string of delimited data. I suggest you look at the link at the bottom of my reply for how to post your question to get better help in the future. But, if my assumption is correct, here is one way of get the results you asked for:

DECLARE @Foo TABLE (V1 VARCHAR(10), V2 VARCHAR(10), V3 VARCHAR(10), V4 VARCHAR(10))
INSERT @Foo VALUES
('6142920211','7048879877','2165874511','3302389299'),
('3307994766',NULL,NULL,NULL),
('2165749877',NULL,'7987451122', NULL),
(NULL,'2168745544',NULL,NULL)


SELECT
	CASE WHEN V1 IS NOT NULL THEN 1 ELSE 0 END
	+ CASE WHEN V2 IS NOT NULL THEN 1 ELSE 0 END
	+ CASE WHEN V3 IS NOT NULL THEN 1 ELSE 0 END
	+ CASE WHEN V4 IS NOT NULL THEN 1 ELSE 0 END
FROM @foo

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

nj8988
Starting Member

3 Posts

Posted - 2013-04-26 : 12:29:46

Thanks for your help, it got me thinking a little bit, and this will work, I just need to know how to group by the aggregated function in the Select statement.

Select
Home_Phone_Number, Work_Phone_Number, Mobile_Phone_Number, Alternate_Phone_Number,
SUM(LEN(Home_Phone_Number) + LEN(Work_Phone_Number) +
LEN(Mobile_Phone_Number) + LEN(Alternate_Phone_Number)) as NUMB

From
[CALL_LIST-WD_AZR_Group3]

GROUP BY Home_Phone_Number, Work_Phone_Number, Mobile_Phone_Number, Alternate_Phone_Number

Here is a view of the result set

http://imgur.com/yEKXj8d

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources