sum from table 1, where from table 2

[John Smith <jayzee.smith@xxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general