0

I have the following tables:

surveys            properties   owners_properties        owners
---+------------   ----------   ------------+---------   ------
id | property_id   id           property_id | owner_id   id

I want to find the count of surveys that have more than one property owners.

Here is a rough stab:

SELECT COUNT(*) FROM surveys 
JOIN properties ON surveys.property_id = properties.id 
JOIN owners_properties...
HAVING count(properties.id > 1);
  • So you are using Postgres? ([psql] is the tag for the command line interface.) What do you get for SELECT version();? And by "more than one property owners", do you really mean "more than one distinct property owners"? – Erwin Brandstetter Dec 30 '15 at 7:25
1

You are close. In SQL Server, you have to GROUP BY before you state HAVING. Personally, I would do something like this

    SELECT COUNT(id) Surveys_w_2_LandOwners
    FROM surveys s
        JOIN 
        (
            SELECT COUNT(1),property_id
            FROM owners_properties
            GROUP BY property_id
            HAVING COUNT(1) > 1
        ) p ON p.property_id=s.property_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.