I have a SQL Statement that is supposed to do this:
Shows a count of Houses, against Country where a Property has 2 matching "People Type" i.e. - now the Property could have 20 products in total, but 2 will have been matched via my criteria below (125 and 111) - the reference numbers are valid in my database and a number of records get record- a list of Level's come back in my SQL Statement below but its showing a bunch of Properties with a count of 1.
What I want is to return the count within Country, the number of Houses within that Country (joined by County and City) that have say 1 Adult Male and 1 Adult Female living there but count the House just once.
Country - is a Country
County is a county within the Country
City is a City within the County, within a Country.
House is a house
People is a type of people, could be "Adult Male","Adult Female","Child Male" and "Child Female".
LivingThere is a table that bridges People to the house and who lives in the house. It has 3 fields, a primary for itself, a HouseRef and a PeopleRef.
SELECT Country.Country,
Country.CountryRef,
COUNT(DISTINCT House.HouseRef) AS [Facility Count]
FROM Country
INNER JOIN County ON Country.CountryRef = County.CountryRef
INNER JOIN City ON County.CountyRef = City.CountyRef
INNER JOIN House ON City.CityRef = House.CityRef
INNER JOIN LivingThere ON House.HouseRef = LivingThere.HouseRef
INNER JOIN People ON LivingThere.LivingThereRef = People.LivingThereRef
WHERE (LivingThere.LivingThereRef IN (125, 111))
GROUP BY Country.Country, Country.CountryRef, House.HouseRef
HAVING (COUNT(LivingThere.LivingThereRef) = 2)
I think Im almost there - Im getting a list of houses that match the people criteria for the countries with the count just showing 1 per record instead of say 1,000 against if. and if I count those houses (by pasting the list into open office for example they seem correct but Im looking for a total sub total, per country.
What have I missed? :/
What I have tried:
Changing the group by and select distinct