sum from table 1, where from table 2

From: John Smith <jayzee(dot)smith(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: sum from table 1, where from table 2
Date: 2014-02-23 18:12:10
Message-ID: CAMZ8qd_-5QsNjHD8N5yfyDWmGEuhna5b766QDEPfjT9kb+WRQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Smith 2014-02-23 18:31:35 Re: sum from table 1, where from table 2
Previous Message Jov 2014-02-23 07:25:00 Re: stand by is starting until I do some work in the primary