Count number of records in a subgroup of a MYSQL query that uses a join

Question

I have two tables that I am accessing in one MYSQL query. From one table I want individual records. From the second table, I want a count of records. The query I am making returns only one row, when I need it to return several. Here's my code:

SELECT c.name, c.city, count(z.zones) 
FROM controllers AS c 
INNER JOIN zones AS z ON c.con_id = z.con_id
WHERE c.user_id = 1;

This returns:
Backyard unit, San Francisco, 21

When I need it to return:
Backyard unit, San Francisco, 6
Side yard controller, Marin, 8
Upper backyard, San Mateo, 7

I've tried many many different strategies to access what I need, and I feel like I'm getting close. It seems like I'm missing something important here.

By the way, I am using PHP to process these results.Thank you in advance for the help. I love this forum. I find so many answers here. But this time I keep coming up short.

Lori

[group by considered useful](http://stackoverflow.com/questions/28497082/mysql-aggregate-functions-without-group-by-clause) — amdixon, Jul 17 '15 at 15:19

score 1 · Accepted Answer · answered Jul 17 '15 at 17:12

1

If you can also group by c.name,c.city

 SELECT c.name, c.city, count(z.zones) 
    FROM controllers AS c 
    INNER JOIN zones AS z ON c.con_id = z.con_id
    group by c.name

answered Jul 17 '15 at 17:12

HashSu

1,507
1
13
13

Yes! This did it. Thank you, @chandran. This was just the nudge I needed. I did learn a lot from reading through the docs as suggested by the other responders. But I hadn't landed on the exact solution yet. As a side note, I found that using "LEFT OUTER JOIN" instead of "INNER JOIN" allowed me to pick up the rows that are null, as well. I really wanted this. Thanks for the help, everyone. I really appreciate it. – Lori Jul 17 '15 at 17:32

Count number of records in a subgroup of a MYSQL query that uses a join

1 Answers1