August 2, 2009 at 7:09 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 9:22 am
It sounds like you just want the count of properties from a specific municipality so your query should be this (note, I changed to standard ANSI join syntax as the old syntax you used will be deprecated):
Select
LUMunicipality.Name AS Mun,
COUNT(PROPERTIES.Municipality) total
From
LUMunicipality JOIN
PROPERTIES ON
LUMunicipality.Name = PROPERTIES.Municipality
WHERE
LUMunicipality.NAME = 'String'
Group By
LUMunicipality.Name
You definitely want the filter in the WHERE as the WHERE is applied BEFORE the GROUP BY so it is more efficient than using HAVING which is applied AFTER the grouping.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply