MySQL query a query and return a count

Question

I have two tables. One called HOSTS and one called GUESTS. The HOSTS table consists of:

* hid
* firstname
* lastname
* email

hid is the unique Host Identifier which is an INTEGER, primary key and auto-incremented.

The GUESTS table consists of:

* gid
* hid
* gfname
* glname

gid is the unique Guest Identifier which is an INTEGER, primary key and auto-incremented.

The desired output is to show on each row the HOSTS and a sum total (figure) of guests each host has.

firstname lastname email "tot num of guests"

How do one accomplish this?

eggyal · Accepted Answer · 2013-11-13 15:44:04Z

3

Join the tables and then group the results:

SELECT   HOSTS.*, COUNT(gid)
FROM     HOSTS LEFT JOIN GUESTS USING (hid)
GROUP BY hid

answered Nov 13, 2013 at 15:44

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Can I ask for an even more advance query here or should I create a new question?

@Andreas: You should really ask a new question.

I've created a new post. Hopefully you can help me out with that one too