I am trying to find the number of animals each state has. I first did this query:

    COUNT(*)  from animals c, locations a where st_contains(a.geom,c.the_geom);

But for some reason I get a syntax error. Can someone guide me in the right direction?

Below are examples of my two tables.

Animals table

      animal_type     city area     the_geom
       9            243582        82F4100000000F0BB0D41

       4           11234         6C892E410000000040170

       3           243582        82F4100000000F0BB0D41

      12          11234         6C892E410000000040170

Location table

     State        gid                  geom

     WV           3                    82F4100000000F0BB0D41

     TX             2                    6C892E410000000040170

closed as off-topic by underdark Nov 5 '16 at 21:56

This question appears to be off-topic. The users who voted to close gave this specific reason:

If this question can be reworded to fit the rules in the help center, please edit the question.


You should use "select" keyword before count(*):

select COUNT(*) from animals c, locations a where ST_Contains(a.geom,c.the_geom);

This returns count of all animals inside all locations (single number). If you need to count animals for each location, you have to use group by syntax:

select a.state, COUNT(*) from animals c, locations a where ST_Contains(a.geom,c.the_geom) group by state;

Not the answer you're looking for? Browse other questions tagged or ask your own question.