[GENERAL] sum from table 1, where from table 2

[John Smith]

i have two tables, like so:

1.
create table public.vote (
        sn      integer primary key,
        total   integer,
        city    varchar(24)
);
sn      | total | city
1       | 11            | new york
2       | 27            | los angeles
3       | 58            | san diego

2.
create table public.state (
        sn              integer primary key,
        st              varchar(2),
        city            varchar(24)
);
sn      | st    | city
1       | ny    | new york
2       | ca    | los angeles
3       | ca    | san deigo

i am looking for a result, like so:
st      | total
ca      | 85
ny      | 11

but this doesn't work:
select  sum(vote.total),
                state.st
from            public.vote,
                public.state
where   vote.city = state.city
group by        state.st
order by        sum(vote.total) desc;

subquery? having?

thanks, jzs


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general