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 |
|
sis-sql_2015
Starting Member
4 Posts |
 Posted - 2015-01-09 : 17:34:18
|
| Sorry for the cryptic and/or misleading subject. Didn't know how to phrase this, lol. Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites. Here's an example of what I'm hoping to accomplish:*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)===================================Temp table===================================SCHOOL | STU_ID | STU_PHONE101 | 12345 | 111-222-3333101 | 23456 | 111-222-3333102 | 34567 | 111-222-3333101 | 45678 | 999-888-7777101 | 56789 | 999-888-7777101 | 67890 | 555-555-5555102 | 78901 | 555-555-5555103 | 89012 | 555-555-5555==================================Wanted query results==================================STU_PHONE | #Students | UNIQUE_SCHOOLS111-222-3333 | 3 | 2999-888-7777 | 2 | 1555-555-5555 | 3 | 3Can someone point me in the right direction?I thank you in advance for any assistance you can provide. I'm also fond of researching and learning, so if you can only provide some topics that I could look up for the answer, that would work as well. Thanks again! |
|
|
singularity
Posting Yak Master
153 Posts |
Posted - 2015-01-09 : 19:07:36
|
| [code]select stu_phone, count(stu_id) as student_cnt, count(distinct school) as uq_school_cntfrom yourtable agroup by stu_phonehaving count(stu_id) > 1[/code] |
 |
|
|
|
|
|
Distinct values among distinct column values