Обсуждение: 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

it works (diego != deigo).

thanks, jzs

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