sum from table 1, where from table 2 - Mailing list pgsql-general

From John Smith
Subject sum from table 1, where from table 2
Date
Msg-id CAMZ8qd_-5QsNjHD8N5yfyDWmGEuhna5b766QDEPfjT9kb+WRQQ@mail.gmail.com
Whole thread Raw
Responses Re: sum from table 1, where from table 2  (John Smith <jayzee.smith@gmail.com>)
Re: sum from table 1, where from table 2  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Jov
Date:
Subject: Re: stand by is starting until I do some work in the primary
Next
From: John Smith
Date:
Subject: Re: sum from table 1, where from table 2