Help with having count - SQL Server Forums

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.

All Forums

Other Forums

MS Access

Help with having count

Author

Topic

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2009-05-11 : 12:54:04

because bun doesn't meet criteria. bun only have 2 custnames. I should have put tibs with diff id number. so let me try this again. Now this table has over 1.6million rows. massive dupes. the columns are id, custname, address, city, state, zip, fone, month, year.

11111 john doe 1111 apple str greenbay wi 1111 1111 10 2008
11111 jane doe 1111 apple str greenbay wi 1111 1111 10 2008
11111 dave doe 1111 apple str greenbay wi 1111 1111 10 2008
11111 june doe 1111 apple str greenbay wi 1111 1111 10 2008
11111 tina doe 1111 apple str greenbay wi 1111 1111 10 2008
11111 tara doe 1111 apple str greenbay wi 1111 1111 10 2008
11111 sara doe 1111 apple str greenbay wi 1111 1111 10 2008
22222 mary bun 2030 green ave greenbay wi 2222 2222 10 2008
22222 sara bun 2030 green ave greenbay wi 2222 2222 10 2008
33333 john tib 2011 lemon ave greenbay wi 2222 1111 10 2008
33333 john tib 2011 lemon ave greenbay wi 2222 1111 10 2008
33333 john tib 2011 lemon ave greenbay wi 2222 1111 10 2008
33333 john tib 2011 lemon ave greenbay wi 2222 1111 10 2008
33333 john tib 2011 lemon ave greenbay wi 2222 1111 10 2008
33333 john tib 2011 lemon ave greenbay wi 2222 1111 10 2008

The goal is to find all id's that have unique custnames >=5 listed within the id. so bun for instance there are only 2 distinct custnames so that doesn't meet the criteria. doe does because there are 7 distinct custnames. tib meets the criteria but you can see it is an exact dupe. they don't want that. they only want all the data within the table but only want anything that is >=5 and want only distinct cust names returned. the reason is right now we send 1 explanation of benefits form to every provider name listed under a tax id. i am using this as an example cuz i can't give you my real data. healthcare is very secure. but no one in reporting dept seems to be able to help me. i know the basic query i need but not sure how to get rid of the duped custname or in this case dupe provname. they want this because instead of sending 1 EOB to john doe, 1 eob to jane doe, 1 eob to dave joe etc etc etc....they want to combine them into 1 eob that goes to the whole group. but before they can program their target list of providers, they need to know who they are. we have to contact them b4 we just flip a switch. healthcare already pisses people off we dont want to add fuel to the fire. this is part of our go green stuff. when we run across a dupe like with tib because all rows are the same, only 1 eob is generated because all rows are exact. everything is exactly the same. so it views it as 1. i know it would be easier to get rid of dupes and it would be easier if the govt would say all docs have to have their own tax id, but that isn't the case. thnks and i hope this makes sense.

possible query:

SELECT t.*
FROM YourTable t
INNER JOIN (SELECT id
FROM YourTable
GROUP BY id
HAVING COUNT(DISTINCT custname) >=5) t1
ON t1.id=t.id

The above doesn't like the distinct in the having count section

tina m miller

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2009-05-11 : 14:29:08

think this might work cuz I tried it and the query is trying to run, just very slowly.

SELECT distinct custname, t.*
FROM YourTable t
INNER JOIN (SELECT id
FROM YourTable
GROUP BY id
HAVING COUNT(custname) >=5) t1
ON t1.id=t.id

tina m miller

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2009-05-11 : 14:48:14

Didn't work now trying this:

SELECT distinct t.*
FROM YourTable t
INNER JOIN (SELECT id
FROM YourTable
GROUP BY id
HAVING COUNT(custname) >=5) t1
ON t1.id=t.id

tina m miller

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2009-05-12 : 08:02:30

SELECT distinct t.*
FROM YourTable t
INNER JOIN (SELECT id
FROM YourTable
GROUP BY id
HAVING COUNT(custname) >=5) t1
ON t1.id=t.id

This isn't working

tina m miller

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-12 : 17:08:06

SELECT custname
from yourtable
GROUP BY custname HAVING Count(*)>=5

Terry

-- Procrastinate now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources