FAQ
What I am looking for is a query that will return a list of id's with a
sum from table b and a sum from table c like this:

id name sum(b) sum(a)
1 shell 34 50
2 jeff 40 20

Thanks
Jeff Meeks
jmeekssr@net-serv.com

P.S. Sorry for sending the reply to you directly Peter I wasn't paying
attention when I hit
the send key

Jeff Meeks writes:
I am trying to join 3 tables
with this query
select a.id, a.name, sum(b.qty), sum(c.qty)
from a, b, c
where a.id=xxx and b.id=a.id and c.id=a.id

what the sums that get returned look as if they are a cross products of
the b and c tables.
It's hard to tell what you want to happen, but perhaps you want two
separate queries:

select a.id, a.name, sum(b.qty) from a, b where a.id=xxx and b.id=a.id
group by a.id, a.name;

and the same with 'c' in place of 'b'.

--

Search Discussions

  • Joel Burton at May 2, 2001 at 6:52 pm

    On Wed, 2 May 2001, Jeff Meeks wrote:

    What I am looking for is a query that will return a list of id's with a
    sum from table b and a sum from table c like this:

    id name sum(b) sum(a)
    1 shell 34 50
    2 jeff 40 20

    Thanks
    Jeff Meeks
    jmeekssr@net-serv.com
    SELECT id, name,
    (SELECT sum(b) FROM b WHERE b.id=a.id) AS sum_b,
    (SELECT sum(c) FROM c WHERE c.id=a.id) AS sum_c
    FROM a;

    --
    Joel Burton <jburton@scw.org>
    Director of Information Systems, Support Center of Washington
  • Nils Zonneveld at May 4, 2001 at 7:07 pm

    Jeff Meeks wrote:

    Hi,
    I am trying to join 3 tables
    with this query
    select a.id, a.name, sum(b.qty), sum(c.qty)
    from a, b, c
    where a.id=xxx and b.id=a.id and c.id=a.id

    what the sums that get returned look as if they are a cross products of
    the b and c tables.

    What is the correct way to join these tables?
    Pointers to docs is welcome

    Thanks
    Jeff Meeks
    jmeekssr@net-serv.com
    I tried it with the folowing data:

    speeltuin=# select * from a;
    id | name
    ----+------
    1 | Joe
    2 | Pete
    3 | John
    (3 rows)

    speeltuin=# select * from b;
    id | qty
    ----+-----
    1 | 1
    1 | 2
    2 | 2
    2 | 3
    (4 rows)

    speeltuin=# select * from c;
    id | qty
    ----+-----
    2 | 4
    2 | 5
    3 | 7
    (3 rows)



    This statement gave the correct result for me:


    select a.id, a.name,
    (select sum(b.qty) from b where b.id = a.id) as b_qty,
    (select sum(c.qty) from c where c.id = a.id) as c_qty
    from a;


    id | name | b_qty | c_qty
    ----+------+-------+-------
    1 | Joe | 3 |
    2 | Pete | 5 | 9
    3 | John | | 7
    (3 rows)

    Maybe someone else has a more efficient one, but this one is correct.

    Hope this helps,

    Nils

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 2, '01 at 4:41p
activeMay 4, '01 at 7:07p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase