CODEWITHSUNDEEP
mysql
Tango Bravo
Tango Bravo

Reputation: 3309

MySQL: Get count on values of different fields in a column

I need to get a count on each unique animal on the city column.

Here's the query that I'm trying:
SELECT city, animals, COUNT(animals) AS count_animals FROM city_animals GROUP BY city

This obviously doesn't work, it's giving results like... 

City           Animals  count_animals
---------       ---     --
Arlington       Cow     1
Austin          Cat     2
Bastrop         Cow     2
Big Sandy       Horse   2
Cedar Creek     Horse   2
Cedar Hill      Cow     2
Chandler        Horse   1
Decatur         Dog     1

I'm looking for something like this: 

City           Animals  count_animals
---------       ---     --
Arlington       Cow     1
Austin          Cat     2
Bastrop         Cow     2
Big Sandy       Horse   2
*Cedar Creek    Horse   1*
*Cedar Creek    Lizard  1*
*Cedar Hill     Cow     1*
*Cedar Hill     Horse   1*
Chandler        Horse   1
Decatur         Dog     1

Here's the modified data that I have: 

City            Animals
-----           --------
Arlington       Cow
Austin          Cat
Austin          Cat
Bastrop         Cow
Bastrop         Cow
Big Sandy       Horse
Big Sandy       Horse
Cedar Creek     Horse
Cedar Creek     Lizard
Cedar Hill      Cow
Cedar Hill      Horse
Chandler        Horse
Decatur         Dog
DeSoto          Cow
DeSoto          Horse
DeSoto          Cow
Duncanville     Llama
Duncanville     Cow
Farmers Branch  Cat
Farmers Branch  Dog
Garland         Lizard
Garland         Cow
Garland         Cow
Garland         Lizard
Garland         Cow
georgetown      Llama
Gladewater      Horse
Hurst           Horse
Kempv           Llama
Mckinney        Dog
Mckinney        Dog
Mckinney        Dog
Midlothian      Horse
Midlothian      Horse
Ovilla          Horse
Ovilla          Horse
Palestine       Horse
Palestine       Horse
Porter          Horse
Porter          Cow
Rockwall        Cow
SPRING          Dog
SPRING          Dog
Terrell         Cow

Upvotes: 0

Views: 68

Answers (1)

Taryn
Taryn

Reputation: 247880

If you want the count for each animal then you will want to GROUP BY both city and animals:

SELECT city, 
  animals, 
  COUNT(animals) AS count_animals 
FROM city_animals 
GROUP BY city, animals

See SQL Fiddle with Demo

Result:

|           CITY | ANIMALS | COUNT_ANIMALS |
--------------------------------------------
|      Arlington |     Cow |             1 |
|         Austin |     Cat |             2 |
|        Bastrop |     Cow |             2 |
|      Big Sandy |   Horse |             2 |
|    Cedar Creek |   Horse |             1 |
|    Cedar Creek |  Lizard |             1 |
|     Cedar Hill |     Cow |             1 |
|     Cedar Hill |   Horse |             1 |
|       Chandler |   Horse |             1 |
|        Decatur |     Dog |             1 |

Upvotes: 3

Related Questions