sum from table 1, where from table 2
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
it works (diego != deigo).
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
On 02/23/2014 10:12 AM, John Smith wrote:
Testing Stefans theory about formatting of email.
test=> select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.2 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.7.1 20120723 [gcc-4_7-branch revision 189773], 32-bit
(1 row)
Yes I know I need to upgrade.
Well trying the above I get:
test=> select
sum(vote.total), st
from
public.vote,
public.state
where
vote.city = state.city
group by st
order by
sum(vote.total) desc;
sum | st
-----+----
85 | ca
11 | ny
(2 rows)
To get the column order you show I did:
test=>select st, sum(vote.total)
from
public.vote,
public.state
where
vote.city = state.city
group by st
order by
sum(vote.total) desc;
st | sum
----+-----
ca | 85
ny | 11
(2 rows)
So I am not sure what is not working on your end?
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general