Question: i need help in select sql statement.

in my mysql database:

location table

```
serialID(AI)|locations | telephone | address
---------------------------------------------
1           | A       
2           | B
3           | C
4           | D

users table
userID | location chosen
-------------------------
1      | A
2      | B
3      | B

```

I want to count the number of people who choose a particular location and display in the table. So if this particular location have more users choosen as their favourite location, it will move up to the first row. May I know how can I do this? 

something like this when it populate into dynamic table ->

```
location | address | telephone | user's favourable
B        |  -      | -         |  2
A        |  -      | -         |  1
C        |  -      | -         |  0
D        |   -     | -         |  0

```

Answer: 
You could just do a query like this:

```
SELECT l.locations, l.telephone, l.address, COUNT (u.userID) as `location_count`
FROM location AS l
LEFT OUTER JOIN users AS u on l.locations = u.location_chosen
GROUP BY l.locations
ORDER BY `location_count` DESC

```
use this:

```
select count(userId) count,locations,address,telephone 
from Table1 Left join Table2 
on Table1.locations = Table2.location

group by locations order by count desc ;

```

see here.. [link](http://sqlfiddle.com/#!2/7ea50/15/0)