Sql group by with a count on a one-to-many relationship

Question

0.00/5 (No votes)

See more:

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

Posted 4-Oct-18 1:46am

Member 12561559

Updated 4-Oct-18 1:54am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-10-04T01:54:00

Solution 1

The problem is that GROUP BY doesn't "refine" results by adding fields to it, it generates more rows with more fields.
Try knocking off the Country.CountryRef and House.HouseRef fields from your GROUP BY clause, and see what happens.

Posted 4-Oct-18 1:54am

OriginalGriff

Comments

Member 12561559 4-Oct-18 8:17am

I just get one record back, with just a total of 1 when I know I have a lot more than that in the data. I had to take the CountryRef out of the select as well as SQL moaned about aggregate in the select.

OriginalGriff 4-Oct-18 8:28am

Of course it did! But adding clauses doesn't do what you want, quite the opposite.
Start here:

https://www.codeproject.com/Articles/1110163/SQL-GROUP-By-and-the-Column-name-is-invalid-in-the

And think about exactly what you are trying to retrieve.

Member 12561559 4-Oct-18 9:51am

Thats great and nice article you wrote there :) I understand how Group By works (and even more so now thanks to your article) so removing the HAVING altogether, I ended up with a count of each time People were living in the house - however, I dont want to count how many people are in the house (in my query above, 2 people) I am getting two results - if I added in a Child Female or Child Male - my counts go up even further - I dont want that ,what I want to do is count the amount of homes, that a Male and Female reside at and count the home once.
For example, if I had
Home A contains 1 Male Adult, 1 Female Adult
Home B contains 1 Male Adult
Home C contains 1 Female Adult
and Home D contains 1 Male Adult, 1 Female Adult and 2 Female Children
and I was to perform a SQL Group By Having clause to see how many homes had 1 Male Adult and 1 Female Adult in there I should see a results list of
Home A and Home D
Counting 2 homes
Doing it by Country, if Home A and Home D were in 2 different countries I would want to see 1 count per country but if they were in the same country, different counties and different cities, I would want to see a count of 2 for that country.

OriginalGriff 4-Oct-18 9:57am

So you want to start by GROUPing the homes together, and then use HAVING to filter that result to the number containing a male and a female.
Start there, get that working, and you can start "moving up the chain" from there. Once you know which homes match, you can JOIN that with the rest of your tables (and possibly GROUP those, I can't see your data) to start producing teh data you want.

Member 12561559 4-Oct-18 10:14am

I just started that before spotting your reply :) I've broken it back down. Basically the example I have given you is a simplified version of what I am actually work on, if we swap homes for "Property" and "People" for "Product", Country, County and City for Level1,Level2 and Level3 I now have:
SELECT DISTINCT Property.PropertyRef
FROM Level1 INNER JOIN
Level2 ON Level1.Level1Ref = Level2.Level1Ref INNER JOIN
Level3 ON Level2.Level2Ref = Level3.Level2Ref INNER JOIN
Property ON Level3.Level3Ref = Property.Level3Ref INNER JOIN
Product_Operator ON Property.PropertyRef = Product_Operator.PropertyRef INNER JOIN
Product ON Product_Operator.ProductRef = Product.ProductRef
WHERE (Product_Operator.ProductRef IN (125, 111))
GROUP BY Property.PropertyRef
HAVING (COUNT(Product_Operator.ProductRef) = 2)

this shows me a list of Properties/Homes that have what I am looking for, but by god it wont let me group it into Country - which is what I will try and work on next - I'll save this bit of SQL so I can return to it when I make a dogs dinner out of trying to expand on it - if I do find the answer then I'll post it up -Thanks Mr.Griff - will give it another go!