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 |
|
acribb
Starting Member
19 Posts |
 Posted - 2006-11-30 : 15:54:13
|
| I am trying to put together a query and I could definitely use anyone's help. Within a table, I am looking at the field named 'actualLoanOfficerId'. I need to select all distinct records where there are 20 or more records that contain the same actualLoanOfficerId. For example, there are 30 records that have the same actualLoanOfficerId of ABCD, so that would be one result I would like to see. Then there are 23 records that have an actualLoanOfficerId of 12345, that is another result I would like to see. Etc., Etc, Can you help with this query?Thanks for any help..... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-30 : 16:03:02
|
| select t.*from yourtable tinner join (select loanofficerid from yourtable group by loanofficerid having count(*) >= 20) qon q.loanofficerid = t.loanofficeridwhere t.leadTypeId = 'Service Quality Survey'Peter LarssonHelsingborg, SwedenEDIT: Edited due to new information in cross posting! |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 16:58:23
|
| "EDIT: Edited due to new information in cross posting!"Blimey! I was reading it over and over trying to work out where you had got the data for the WHERE clause from before I saw that extra EDIT: comment!acribb: Please don't cross post, it just results in different people providing information in the various threads and folk getting annoyed that they spend time answering only to find that critical information is added in another thread.Kristen |
|
|
|
acribb
Starting Member
19 Posts |
Posted - 2006-11-30 : 17:01:25
|
| Sorry, my first time posting. I realized I put it in the wrong place so I posted it on the T-SQL forum instead. Mods, please delete one of the posts for me if you can. |
|
|
|
acribb
Starting Member
19 Posts |
Posted - 2006-11-30 : 17:04:05
|
| Also, I don't think this is working.When I sort the results by actualLoanOfficerId, I can see that the first group of records have only 14 records with an actualLoanOfficerId rather than the required 20 or more.Any ideas? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 17:09:30
|
| "I can see that the first group of records have only 14 records"Is the offending record in:select loanofficerid from yourtable group by loanofficerid having count(*) >= 20??Kristen |
|
|
|
acribb
Starting Member
19 Posts |
Posted - 2006-11-30 : 17:26:57
|
| This seems to have worked:select actualLoanOfficerId, count(*) as ct from dbo.UniversalLeads where leadTypeId = 'Service Quality Survey'group by actualLoanOfficerIdhaving count(*) >= 20 Thanks again for everyone's help. I'll be back soon! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-01 : 00:09:01
|
| So you want where there are 20 or more records that contain the same actualLoanOfficerId AND leadTypeId = 'Service Quality Survey'Is that right?Kristen |
|
|
|
|
|
|
|
|
Query help - I think this one is easy......