Trying to find distinct records in a subset

Folks,

I am a consultant new to this client and new to Sybase.  I am working in the telecommunications industry and a client wants to know an active subscriber count in a particular group of counties, broken down by county.  Then they wanted to narrow it further by only having a certain Billing Code.  Now they want a list of clients from this existing report that don't have phone service.  The problem is, if I understand it correctly, there are multiple records for each subscriber, when the digital_phone_service field is brought into the query.  There are two potential values in that column, a 0, meaning the client doesn't have digital_phone_service and a one when they do.  I basically want to select only one of the records with a zero in the digital_phone_service field.  I have played with this a bit and I am pretty sure a subquery is in order here and I have played with variations on this ( including distinct ), but I am not getting the desired results ).  Can someone please assist here?  Thanks so much.

Scott
select cnt_county_name, SBP_SUBSCRIBER_NUMBER, sbs_bill_code, sbs_service_code, srm_digital_phone_service
from dbo.SUBSCRIBER_PACKAGE s, county, subscriber_service, service_ext_matrix
where county.CNT_SYS_PRIN_ID = sbp_sys_prin_id
and   county.CNT_AGENT_ID = sbp_agent_id
and   s.SBP_SUBSCRIBER_NUMBER = sbs_subscriber_number
and   s.SBP_SYS_PRIN_ID = sbs_sys_prin_id
and   s.SBP_AGENT_ID = sbs_agent_id
and   SBS_SYS_PRIN_ID = srm_sys_prin_id
and   SBS_AGENT_ID = srm_agent_id
and   subscriber_service.SBS_SERVICE_EXT_CODE = srm_service_ext_code
and  SBP_SYS_PRIN_ID = '82231500'
and  subscriber_service.SBS_BILL_CODE = 'K5'
--and service_ext_matrix.SRM_DIGITAL_PHONE_SERVICE = 0
and s.SBP_SUBSCRIBER_NUMBER = '8223150250000459'
--group by cnt_county_name
--order by cnt_county_name

Open in new window

flscooterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grant300Commented:
The trick is to GROUP BY on the Subscriber Number (plus some other columns), take the SUM() aggregate of the Digital Phone Service field, and use a HAVING clause.

Something like the snippet below.

BTW, a couple of notes on the SQL...
First, When you have long table names that you are not going to retype in front of each variable, ALWAYS use an alias (correlation name) and always prefix the fields with it.  It makes it much, much easier to read, understand, and maintain.  Even if the field names are unique, it was not clear which fields belong with which table.

Second, I find that using the ANSI SQL JOIN syntax makes the SQL far easier to read and removes the ambiguity.  Your example is a perfect case where using the FROM tblname JOIN tblname ON ..... JOIN tblname....etc syntax would have made it substantially less work to understand.

Regards,
Bill

SELECT cnt_county_name, SBP_SUBSCRIBER_NUMBER, sbs_bill_code, sbs_service_code
  FROM dbo.SUBSCRIBER_PACKAGE s,
       county,
       subscriber_service,
       service_ext_matrix
 WHERE county.CNT_SYS_PRIN_ID = sbp_sys_prin_id
   AND county.CNT_AGENT_ID = sbp_agent_id
   AND s.SBP_SUBSCRIBER_NUMBER = sbs_subscriber_number
   AND s.SBP_SYS_PRIN_ID = sbs_sys_prin_id
   AND s.SBP_AGENT_ID = sbs_agent_id
   AND SBS_SYS_PRIN_ID = srm_sys_prin_id
   AND SBS_AGENT_ID = srm_agent_id
   AND subscriber_service.SBS_SERVICE_EXT_CODE = srm_service_ext_code
   AND SBP_SYS_PRIN_ID = '82231500'
   AND subscriber_service.SBS_BILL_CODE = 'K5'
   AND s.SBP_SUBSCRIBER_NUMBER = '8223150250000459'
 GROUP BY cnt_county_name, SBP_SUBSCRIBER_NUMBER, sbs_bill_code, sbs_service_code
HAVING SUM(srm_digital_phone_service) = 0

Open in new window

0
flscooterAuthor Commented:
Bill,

Thanks so much for responding, I really appreciate your help.  First, let me apologize for the readability of my SQL.  I am using a tool called WinSQL, which, like VBA, prompts you for the table and field names when you start typing them, amd the tool places the full name in.  This makes it ( supposedly ) easier to code ( but unfortunately, not read ).  You are certainly correct about the alias making the query easier to read.  As far as using the ANSI SQL join syntax, I guess I am just old school, but yes, that would make the query easier to understand as well, point taken.  As for my query, I am in a quandry here.  The query I showed you was a query I was using to help me figure out a portion of my final query.  That is, I changed the original query to work on one subscriber, to fix the inherent problem of multiple records per subscriber.  I believe I solved this late yesterday, while waiting for your response.  Unfortunately, I didn't solve my overall problem.  I am not sure if I need to post another question on how to take what I learned in the query you just helped me with, or just put it here with this one, as they are essentially the same.  I will post two code snippets below.  The first one is my solution to get only 1 record per account, which is nothing more than realizing I didn't need the other columns in the select statement but the county name and a count distinct of each subscriber number.  Once I simplified things, I believe I got the result I was looking for.  I am just not sure how to apply it when I take out the code looking for an individual subscriber.  Hopefully, this isn't too confusing and my explanation is understandable.  The second query attached below is the overall query I was ultimately trying to solve.  Thanks again.

Scott
My updated code ( which you helped on ) to get only 1 record for a particular subscriber:
select cnt_county_name, count(distinct SBP_SUBSCRIBER_NUMBER)--, sbs_bill_code, srm_digital_phone_service--,sbs_service_code,
from dbo.SUBSCRIBER_PACKAGE s, county, subscriber_service, service_ext_matrix
where county.CNT_SYS_PRIN_ID = sbp_sys_prin_id
and   county.CNT_AGENT_ID = sbp_agent_id
and   s.SBP_SUBSCRIBER_NUMBER = sbs_subscriber_number
and   s.SBP_SYS_PRIN_ID = sbs_sys_prin_id
and   s.SBP_AGENT_ID = sbs_agent_id
and   SBS_SYS_PRIN_ID = srm_sys_prin_id
and   SBS_AGENT_ID = srm_agent_id
and   subscriber_service.SBS_SERVICE_EXT_CODE = srm_service_ext_code
and  SBP_SYS_PRIN_ID = '82231500'
and  subscriber_service.SBS_BILL_CODE = 'K5'
and service_ext_matrix.SRM_DIGITAL_PHONE_SERVICE = 0 
--in (select distinct srm_digital_phone_service
                                                   
--from dbo.SERVICE_EXT_MATRIX
                                                  
 --where SRM_DIGITAL_PHONE_SERVICE = 0 )
 
and s.SBP_SUBSCRIBER_NUMBER = '8223150250000459'
group by cnt_county_name
order by cnt_county_name
 
This query is my main query, pertaining to all subscribers in particular counties.  I am not sure how to apply what I just learned above:
select cnt_county_name, count(distinct SBP_SUBSCRIBER_NUMBER)
from dbo.SUBSCRIBER_PACKAGE s, county, subscriber_service, service_ext_matrix
where county.CNT_SYS_PRIN_ID = sbp_sys_prin_id
and   county.CNT_AGENT_ID = sbp_agent_id
and   s.SBP_SUBSCRIBER_NUMBER = sbs_subscriber_number
and   s.SBP_SYS_PRIN_ID = sbs_sys_prin_id
and   s.SBP_AGENT_ID = sbs_agent_id
and   SBS_SYS_PRIN_ID = srm_sys_prin_id
and   SBS_AGENT_ID = srm_agent_id
and   subscriber_service.SBS_SERVICE_EXT_CODE = srm_service_ext_code
and  SBP_SYS_PRIN_ID = '82231500'
and  subscriber_service.SBS_BILL_CODE = 'K5'
and service_ext_matrix.SRM_DIGITAL_PHONE_SERVICE = 0
group by cnt_county_name
order by cnt_county_name

Open in new window

0
grant300Commented:
Remove line 16 from the query I supplied you with and you should get it for all subscribers.

The only possible hitch is I have no idea what the SBP_SYS_PRIN_ID is but it appears to share the first digits wtih teh SBP_SUBSCRIBER_NUMBER.  I suspect that if your subscriber numbers fall outside whatever range is denoted by the SBP_SYS_PRIN_ID, you will not get "all" subscribers.

Regards,
Bill
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flscooterAuthor Commented:
Bill,
I want to thank you so much for your prompt and curteous responses.  Subscriber number refers to an individual household while sys_prin_id refers to a region that household belongs in.  While I didn't use your query exactly, you helped me to ultimately get to my solution.  Thanks again for your time, help, and expertise.  All the best.

Scott
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.