SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count of NON-NULL fields across
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nj8988
Starting Member

3 Posts

Posted - 04/26/2013 :  11:14:29  Show Profile  Reply with Quote
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

India
85 Posts

Posted - 04/26/2013 :  11:30:13  Show Profile  Reply with Quote
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
Go to Top of Page

satheesh
Posting Yak Master

United Kingdom
146 Posts

Posted - 04/26/2013 :  11:45:01  Show Profile  Reply with Quote
Have a look and try some thing like this
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9fb882b0-854a-4124-8a92-2b70e272e1ae/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/26/2013 :  12:04:48  Show Profile  Reply with Quote
@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

Edited by - Lamprey on 04/26/2013 12:05:33
Go to Top of Page

nj8988
Starting Member

3 Posts

Posted - 04/26/2013 :  12:29:46  Show Profile  Reply with Quote
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


Edited by - nj8988 on 04/26/2013 12:39:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000