August 2, 2009 at 7:15 pm
LUMunicipality table consists of
id, Name, Main fields
Would like to further filter results from the following:
Select LUMunicipality.Name AS Mun,COUNT(PROPERTIES.Municipality)total
From LUMunicipality,PROPERTIES Where LUMunicipality.Name =
PROPERTIES.Municipality Group By LUMunicipality.Name
returns
MUN total
city1 17
city2 1
city3 1
city4 34
city5 15
city6 16
city7 18
city8 20
city9 27
city10 1
city11 5
I need to further filter this result set based on a string value in the 'Main' field in the LUMunicipality table
ie; where Main = 'a string value'
appending HAVING will not work with strings..correct?
Any direction would be appreciated. Thanks
August 3, 2009 at 2:22 am
appending HAVING will not work with strings..correct?
Not correct... you can use HAVING with strings, but not with BLOB columns e.g. TEXT.
The HAVING clause is effectively a WHERE clause, but acting after the GROUP.
If you add your filter as a HAVING, you are forcing SQL Server to group some values, just to throw them away. If you add it as a WHERE clause, you are allowing SQL Server to throw away these values before the GROUP phase.
You are also using non-ansi join syntax. Try this
Select LUMunicipality.Name AS Mun,COUNT(PROPERTIES.Municipality)total
From LUMunicipality
INNER JOIN PROPERTIES ON LUMunicipality.Name =PROPERTIES.Municipality
WHERE Main = 'a string value'
Group By LUMunicipality.Name
August 3, 2009 at 8:18 am
Thanks IanScarlett that resolved nicely and queued me to research more.
Thanks again
b
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply