December 29, 2009 at 6:08 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 7:01 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!! |
December 29, 2009 at 7:13 pm
Thanks for the answer, now it's coming back with:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'PracticeAddressFirstLine'.
December 29, 2009 at 7:15 pm
Just place a comma "," after nName
Name as nName,
| If in Doubt...don't do it!! |
December 29, 2009 at 7:19 pm
I figured it out! It was missing the "," here:
SELECT COUNT(Name) as nCount,
Name as nName,[/size]
PracticeAddressFirstLine as PAF
FROM GlobalOrganization
WHERE PracticeAddressState = 'TX'
GROUP BY Name,PracticeAddressFirstLine
ORDER BY nCount
Thanks so much for your help!
December 29, 2009 at 7:21 pm
Well you've come to the right place 😀
Glad to help
| If in Doubt...don't do it!! |
December 29, 2009 at 8:18 pm
I wanted to take all of the Addresses that are same and count them regardless of the Name. So I tried:
SELECT COUNT(PracticeAddressFirstLine) as nCount,
Name as nName,
PracticeAddressFirstLine as PAF
FROM GlobalOrganization
WHERE PracticeAddressState = 'TX'
GROUP BY PracticeAddressFirstLine, Name
ORDER BY nCount, PracticeAddressFirstLine, Name
But this still totals by Name
So I tried:
SELECT COUNT(PracticeAddressFirstLine) as nPracticeAddressFirstLine,
Name as nName,
PracticeAddressFirstLine as PAF
FROM GlobalOrganization
WHERE PracticeAddressState = 'TX'
GROUP BY PracticeAddressFirstLine, Name
ORDER BY nCount, PracticeAddressFirstLine, Name
This gives me an error:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'nCount'.
This is confusing me as no where in the above code is a column name nCount?
December 29, 2009 at 8:25 pm
The error occurs in the ORDER BY clause as no columns by the name of nCount exists
replace :
ORDER BY nCount, PracticeAddressFirstLine, Name
with
ORDER BY nPracticeAddressFirstLine,PracticeAddressFirstLine, Name
| If in Doubt...don't do it!! |
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply