CODEWITHSUNDEEP
mysqlsql
user5511204
user5511204

Reputation:

Counting houses for each person in MySQL

I have 2 tables: Person and House. Each person has a name, email, and a phone number, while a house has a number of rooms and a city location. I have to show every person name and a number of houses, where the number of houses is the number of how many houses a person has.

This is seems to be a one-has-many relationship. So here is my query for the Person Table:

CREATE TABLE Person ( person_id INT NOT NULL PRIMARY KEY, 
       name VARCHAR(15) NOT NULL, 
       email VARCHAR(320) NOT NULL,
       phone_number VARCHAR(15) NOT NULL);

and here is the query of the House Table:

CREATE TABLE House (
    house_id INT NOT NULL PRIMARY KEY,
    person_id INT NOT NULL,
    number_of_rooms INT NOT NULL,
    city_location VARCHAR(15) NOT NULL,
    FOREIGN KEY(person_id) REFERENCES Person(person_id) );

As to show how many houses each person has, I wrote this query:

SELECT name AS "Person name", count(house_id) AS "Number of houses" FROM Person JOIN House WHERE Person.person_id = House.person_id;

But that query only shows only the first name I've inserted (inserted more) and the sum of house_id for that name (for house_id #1 & #2 & #3 the result should be as 3, but it show 6) and that's it.

When I thought of it for the first time, it's really easy to build, I don't know why I'm wasting so much time on this. Any help?

Upvotes: 0

Views: 1600

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You need a group by. And, you should use an ON clause when you use JOIN. I would also recommend table aliases. They make the query easier to write and to read:

SELECT p.name AS "Person name", count(h.house_id) AS "Number of houses"
FROM Person p JOIN
     House h
     ON p.person_id = h.person_id
GROU PBY p.name;

Upvotes: 1

Alex
Alex

Reputation: 14628

SELECT p.name AS "Person name", count(h.house_id) AS "Number of houses" 
FROM Person p
LEFT JOIN House h ON h.person_id = p.person_id
GROUP BY p.name

This is what you need.

What I changed were the following:

  • Added aliases to tables (comes in handy)
  • Used a LEFT OUTER JOIN (LEFT JOIN for short) instead of an INNER JOIN (JOIN for short) in order to get even the people that have no matching house (number of houses will be zero for them)
  • Added the ON clause for JOIN (required for syntax)
  • Added a GROUP BY clause. Required for using aggregate functions like COUNT

Upvotes: 1

Related Questions