January 12, 2010 at 12:50 pm
2 tables
LUMunicipality = Name, MainMun (Name is unique data per row) (MainMun contains dups)
Name MainMun
city1 Area1
city2 Area2
city3 Area1
PROPERTIES
Municipality Municipality2, Municipality3
city1 -------- city3
city2 -------- city4 -------- city3
city1 -------- city6 -------- city3
To achieve a count on the number of entries from PROPERTIES.Municipality that match
LUMunicipality.Name
BELOW statement works well ..all good
Select LUMunicipality.Name AS Municipality,COUNT(PROPERTIES.Municipality)'Properties Available'
From LUMunicipality
INNER JOIN PROPERTIES ON LUMunicipality.Name =PROPERTIES.Municipality
WHERE LUMunicipality.MainMun = 'Rice Lake'
AND Expired=0
Group By LUMunicipality.Name
However I am stumped to get a count based on PROPERTIES.Municipality and/or PROPERTIES.Municipality2 and/or PROPERTIES.Municipality3 as COUNT accepts only one argument.
I would like to get a representative count on Area1 from all Municipality columns which using the example would be 5.
Hopefully I have explained it well enough
Any insight other than laughter would be appreciated 😉
January 12, 2010 at 2:06 pm
There are a couple of options that spring to mind:
You could use UNPIVOT to return one column to count on and 1 column to filter on.
Another option is you could perform your inner join on municipality.name = properties.municipality OR municipality.name = properties.municipality3 OR municipality.name = properties.municipality3 and conditionally count them viz.
SELECT LUMunicipality.Name AS Municipality,
SUM(case when PROPERTIES.Municipality = LUMunicipality.Name then 1 else 0 end +
CASE WHEN PROPERTIES.Municipality2 = LUMunicipality.Name then 1 else 0 end +
CASE WHEN PROPERTIES.Municipality3 = LUMunicipality.Name then 1 else 9 end) as From LUMunicipality
INNER JOIN PROPERTIES
ON LUMunicipality.Name =PROPERTIES.Municipality
OR LUMunicipality.Name =PROPERTIES.Municipality2
OR LUMunicipality.Name =PROPERTIES.Municipality3
WHERE LUMunicipality.MainMun = @MainMum
AND Expired=0
These would work with the schema you have, the way it is.
SQL guy and Houston Magician
January 12, 2010 at 3:25 pm
not sure where your expired field is, so you will need to add to the appropriate where clause. This query will count the Properties available for a specific LUMunicipality.MainMun, I believe this is what you were asking for.
It really helps if you post structures and data so we can test a little better, but good luck, hope it helps
select LUMunicipality.MainMun, sum(total) as 'Properties Available' from
(
Select Municipality,count(Municipality) total from PROPERTIES group by Municipality
union
Select Municipality2,count(Municipality2) total from PROPERTIES group by Municipality2
union
Select Municipality3,count(Municipality3)total from PROPERTIES group by Municipality3
)AllProperties INNER JOIN LUMunicipality ON LUMunicipality.Name =AllProperties.Municipality
group by LUMunicipality.MainMun
January 13, 2010 at 9:19 am
Thanks Robert
Thanks jcdyntek
I utilized Roberts example (exactly what I needed) with some minor changes and retrofit to real structure.
Much appreciated.
For anyone interested here is a working revision using five columns and returns results such as this:
RICE LAKE #(the count based on SUM)
SELECT LUMunicipality.Name AS Municipality,
SUM(case when PROPERTIES.Municipality = LUMunicipality.Name then 1 else 0 end +
CASE WHEN PROPERTIES.Municipality_Alt1 = LUMunicipality.Name then 1 else 0 end +
CASE WHEN PROPERTIES.Municipality_Alt2 = LUMunicipality.Name then 1 else 0 end +
CASE WHEN PROPERTIES.Municipality_Alt3 = LUMunicipality.Name then 1 else 0 end +
CASE WHEN PROPERTIES.Municipality_Alt4 = LUMunicipality.Name then 1 else 0 end)'Properties Available' From LUMunicipality
INNER JOIN PROPERTIES
ON LUMunicipality.Name =PROPERTIES.Municipality
OR LUMunicipality.Name =PROPERTIES.Municipality_Alt1
OR LUMunicipality.Name =PROPERTIES.Municipality_Alt2
OR LUMunicipality.Name =PROPERTIES.Municipality_Alt3
OR LUMunicipality.Name =PROPERTIES.Municipality_Alt4
WHERE LUMunicipality.MainMun = 'Rice Lake'
AND Expired=0
Group By LUMunicipality.Name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply