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.
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,3302389299Row 2: 3307994766,,,Row 3: 2165749877,,7987451122Row 4: ,2168745544,,Row 1 would return 4Row 2 would return 1Row 2 would return 2Row 3 would return 1Is 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 endfrom table_namerepalce 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 ENDFROM @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. SelectHome_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 NUMBFrom [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 sethttp://imgur.com/yEKXj8d |
|
|
|
|
|
|
|