mysql query to get count from a table

Question

I have 3 tables like this:

Areas 
id      city_id         area
1       1               mayfield
2       2               cleveland

neighbourhood
id      neighbourhood   area_id
101     cayahoga        1
102     milan           1
103     downtown        2

Salons
id      neighbourhood_id
3       101
4       101
5       102

I am trying to get count of all Salons in all areas.

Like salons in areas (mayfield = 2, cleveland = 1)

Is it possible to do this in 1 query?

xjeroen · Accepted Answer · 2014-11-26 13:23:19Z

1

SELECT areas.area,COUNT(Salons.id)
FROM areas
LEFT JOIN neighbourhood ON neighbourhood.area_id = areas.id
LEFT JOIN Salons ON Salons.neighbourhood_id = neighbourhood.id
GROUP BY areas.id

answered Nov 26, 2014 at 13:23

xjeroen

283 bronze badges

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

Comments

mlinth · Accepted Answer · 2014-11-26 13:39:34Z

1

select area,count(s.id) as salon_count FROM
Areas a
INNER JOIN neighbourhood n
ON a.id = n.area_id
INNER JOIN
Salons s on s.neigbourhood_id = n.id
GROUP BY area

edited Nov 26, 2014 at 13:39

answered Nov 26, 2014 at 13:21

mlinth

3,1586 gold badges35 silver badges32 bronze badges

2 Comments

NoviceMe Over a year ago

not giving count like: mayfield 2, cleveland 1. It is just giving count = 3?

mlinth Over a year ago

Not sure why that wouldn't work. I've just changed it to "count(s.id)"... Should work now...

Collectives™ on Stack Overflow

mysql query to get count from a table

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related