Re: Programming in SQL?

A copy of this was sent to amerar_at_ci.chi.il.us (if that email address didn't require changing) On Tue, 26 Oct 1999 17:36:18 GMT, you wrote:

>
>
>
>Hello,
>
>I am trying to create a very interesting query and I'm not sure if it
>can be done.
>
>I have two tables: Table A and Table B. There is a one to many
>relationship from Table A to Table B. What I want to do, for the dollar
>amount fields, is for matching records in the tables, I want sum the
>records in Table B first, then add it to the field in Table A.
>
>So,
>
>Table A has one record with a dollar amount of $100.
>Table B has three records with dollar amounts $100, $100 and -$100.
>
>I want to sum those records in Table B BEFORE adding them to Table A.
>
>Can this be done or am I stuck writing a PL/SQL procedure?
>
>Please send a copy to my e-mail.
>

tkyte_at_8i> create table a ( id int, dollar int ); Table created.

tkyte_at_8i> create table b ( id int, dollar int ); Table created.

tkyte_at_8i> insert into a values ( 1, 100 );
tkyte_at_8i> insert into b values ( 1, 100 );
tkyte_at_8i> insert into b values ( 1, 100 );
tkyte_at_8i> insert into b values ( 1, -100 );

tkyte_at_8i>
tkyte_at_8i> select a.id, a.dollar + sum(b.dollar)   2 from a, b
  3 where a.id = b.id
  4 group by a.id, a.dollar
  5 /

        ID A.DOLLAR+SUM(B.DOLLAR)

---------- ----------------------
         1                    200

>Thanks,
>
>Arthur
>amerar_at_ci.chi.il.us
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Oct 26 1999 - 13:05:41 CDT