[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trying to find distinct records in a subset

Posted on 2008-11-17
4
Medium Priority
?
813 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:flscooter
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 22980114
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
 

Author Comment

by:flscooter
ID: 22984917
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
 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 22986742
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
 

Author Closing Comment

by:flscooter
ID: 31517593
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question