December 29, 2009 at 6:12 pm
I am trying to run the following query:
select Name, PracticeAddressFirstLine from GlobalOrganization where PracticeAddressState = 'TX' group by practiceaddressfirstline order by Name
I get the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'GlobalOrganization.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The records look like this when I run the query without "group by":
Name __________________________________________________ PracticeAddressFirstLine
BAYLOR COLLEGE OF MEDICINE _____________________________ 1 BAYLOR PLZ
BAYLOR COLLEGE OF MEDICINE _____________________________ 1 BAYLOR PLZ
BAYLOR COLLEGE OF MEDICINE/NEUROLOGY ASSOCIATES _______ 1 BAYLOR PLZ
BAYLOR GENETICS ________________________________________ 1 BAYLOR PLZ
BAYLOR PATHOLOGY CONSULTANTS _________________________ 1 BAYLOR PLZ
OUTREACH HEALTH SERVICES ______________________________ 10 BRIERCROFT OFFICE PARK
OUTREACH HEALTH SERVICES ______________________________ 10 BRIERCROFT OFFICE PARK
(sorry for the underscores above, it was stripping spaces and tabs and I was trying to show the two columns.)
From the above example, I would like to know that there is one instance where there were five addresses that were exactly the same and one instance where there were two addresses that were exactly the same.
If it's not too difficult, also that it found two instances where the Names were exactly the same for those addresses that were the same (the first two records) and (the last two records) or maybe where the first word (instead of the entire field) in the first five records were exactly the same and the last two records were exactly the same.
The end result I'm looking for is: How many times do I have a situation where an address is duplicated in the database, and when it happens, how many times that address is repeated. So I would end up with something like:
Where addresses are repeated 10 times, you have 100 entries.
Where addresses are repeated 9 times, you have 200 entries.
... 8,7,6,5,4,3,
Where addresses are repeated 2 times, you have 32,000 entries.
I'm trying to get a feel for how much work it's going to take to clean up this database.
This is the first time I've ever touched SQL server so sorry for the NOOB question.
December 29, 2009 at 6:56 pm
I am trying to run the following query:
select Name, PracticeAddressFirstLine from GlobalOrganization where PracticeAddressState = 'TX' group by practiceaddressfirstline order by Name
I get the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'GlobalOrganization.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The GROUP BY will only work with AGGREGATE FUNCTIONS like SUM(),COUNT() etc :
try:
SELECT COUNT(Name) as nCount,
Name as nName
PracticeAddressFirstLine as PAF
FROM GlobalOrganization
WHERE PracticeAddressState = 'TX'
GROUP BY Name,PracticeAddressFirstLine
ORDER BY nCount
| If in Doubt...don't do it!! |
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply