July 1, 2011 at 11:25 am
Trying to figure out how to get list of only those AgencyNames that have more than one AgencyID assigned. My query so far:
SELECT AgencyName, AgencyID
FROM ContactAction
GROUP BY AgencyName, AgencyID
ORDER BY AgencyName
If there are three AgencyIDs assigned, the AgencyName will be listed three times in succession, with the associated AgencyID in the second column.
Any help appreciated!! ~Carl
July 1, 2011 at 12:07 pm
chornung (7/1/2011)
Trying to figure out how to get list of only those AgencyNames that have more than one AgencyID assigned. My query so far:SELECT AgencyName, AgencyID
FROM ContactAction
GROUP BY AgencyName, AgencyID
ORDER BY AgencyName
If there are three AgencyIDs assigned, the AgencyName will be listed three times in succession, with the associated AgencyID in the second column.
So there's a new record in ContactAction for each Agency/AngencyId; why not:
SELECT AgencyName, COUNT(*)
FROM ContactAction
GROUP BY AgencyName
HAVING COUNT(*) > 1
ORDER BY AgencyName
July 1, 2011 at 12:44 pm
Sorry, guess I wasn't clear, as that counts the number of actual records for each agency.
There are hundreds of records in the db for each agency. However, some are listed under one AgencyID, others for the same AgencyName but a different AgencyID.
I'm not interested here in how many records there are for an agency (e.g., 600 for AgencyA under ID #AB123 and 200 for AgencyA under ID#YZ456). All I need is a list showing AgencyA, AB123 and AgencyA, YZ456 so Resource people know which AgencyIDs they need to combine.
July 1, 2011 at 1:03 pm
chornung (7/1/2011)
Sorry, guess I wasn't clear, as that counts the number of actual records for each agency.There are hundreds of records in the db for each agency. However, some are listed under one AgencyID, others for the same AgencyName but a different AgencyID.
I'm not interested here in how many records there are for an agency (e.g., 600 for AgencyA under ID #AB123 and 200 for AgencyA under ID#YZ456). All I need is a list showing AgencyA, AB123 and AgencyA, YZ456 so Resource people know which AgencyIDs they need to combine.
Sorry, didn't understand what you were after. Howabout something like:
SELECT DISTINCT AgencyName, AgencyId
INTO #MyTempTable
FROM ContactAction
SELECT AgencyName, COUNT(*)
FROM #MyTempTable
GROUP BY AgencyName
HAVING COUNT(*) > 1
ORDER BY AgencyName
HTH,
Rob
July 1, 2011 at 2:07 pm
That gives me the number of different IDs associated with each AgencyName, and only lists those with more than one ID. But how can I get the associated AgencyID to print with the AgencyName, so I end up with something like this:
AgencyA ABC123
AgencyA XYZ456
AgencyB LMN789
AgencyB OPQ098
AgencyB RST234
Resource people will be able to look at that list and know they have to tell me which ID should be used for AgencyA (e.g., change all XYZ456 to ABC123, etc). No AgencyName should print if there is only one ID assigned.
July 6, 2011 at 4:55 am
try subquery such as --->
SELECT agencyName,agencyId
FROM contactAction
WHERE EXISTS
(
SELECT agencyName,count(*)
FROM contactAction
GROUP BY agencyName
HAVING COUNT(*) <> 1
)
July 6, 2011 at 5:49 am
[font="Times New Roman"]
Hi,
You may try following script.
SELECT AgencyName,AgencyId
FROM ContactAction
WHERE AgencyName IN
(
SELECT AgencyName
FROM contactAction
GROUP BY AgencyName
HAVING COUNT(*)>1
)
ORDER BY AgencyName
Regards,
Anil Kumar
[/font]
July 6, 2011 at 4:10 pm
Kritika -
I thought for sure that would work, but it gives me the agency name twice with the same AgencyID twice, e.g.,
Disability Network UWSE281
Disability Network UWSE281
rather than
Disability Network UWSE281
Disability Network ABCD890
Your suggestion gives me a good start; now I just need to play with it until I stumble on the solution. Meanwhile, I was a bit under the gun so I used Excel pivot table to get what I needed. Not pretty, but it gave them what they needed. But thanks so much for the attempt!
July 6, 2011 at 4:22 pm
anil_kumar32 (7/6/2011)
[font="Times New Roman"]Hi,
You may try following script.
SELECT AgencyName,AgencyId
FROM ContactAction
WHERE AgencyName IN
(
SELECT AgencyName
FROM contactAction
GROUP BY AgencyName
HAVING COUNT(*)>1
)
ORDER BY AgencyName
Regards,
Anil Kumar
[/font]
Anil - Like the other post, I thought for sure this would work. The only thing missing is a grouping by AgencyName and AgencyID, as it produces a long list of each record. Unless you have another idea, I just need to play with it until I hit on the right solution. But thanks for taking the time to reply. I really appreciate it!
July 6, 2011 at 6:39 pm
Is this what you are after?
SELECT AA.AgencyName,AA.AgencyID
FROM (
SELECT CA.AgencyName, CA.AgencyID, COUNT(*) OVER(PARTITION BY CA.AgencyName) AS AgencyIDCount
FROM ContactAction AS CA
GROUP BY CA.AgencyName, CA.AgencyID
) AS AA
WHERE AA.AgencyIDCount>1
ORDER BY AA.AgencyName
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);July 6, 2011 at 7:10 pm
mister.magoo (7/6/2011)
Is this what you are after?
SELECT AA.AgencyName,AA.AgencyIDFROM (
SELECT CA.AgencyName, CA.AgencyID, COUNT(*) OVER(PARTITION BY CA.AgencyName) AS AgencyIDCount
FROM ContactAction AS CA
GROUP BY CA.AgencyName, CA.AgencyID
) AS AA
WHERE AA.AgencyIDCount>1
ORDER BY AA.AgencyName
My friend, that's EXACTLY the output I was looking for. 😛 When my head is a bit clearer, I will analyze what you did, and [hopefully] learn from it. When I grow up, I want to be just like you!! Thanks! ~Carol
July 6, 2011 at 10:48 pm
exists keyword has its own flaw... you can try the IN keyword( reff in one of the posts)... but make sure to give only 1 select column name in the sub query.
exists fails at times wen inner quesry returs null i donno which 1 is better performance wise thou!
July 8, 2011 at 1:07 am
chornung,
would be easy for us to suggest if you provide us some test data along with table structure.
July 8, 2011 at 5:34 am
Will do next time. Thanks!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply