Re: Complex qry

From: <ddf_dba_at_my-deja.com>
Date: 2000/06/29
Message-ID: <8jg9bi$qkk$1@nnrp1.deja.com>#1/1

In article <8jfvi0$ibs$1_at_nnrp1.deja.com>,   prince_kumar_at_my-deja.com wrote:
> Hi:
>
> I have the following two tables,
>
> c_table:
>
> login_id c_id c_tot
> ---------------------------
> 10 10 10
> 10 20 20
> 20 10 300
> 30 20 100
> 30 40 250
>
> g_table:
>
> login_id g_id g_tot
> -----------------------------
> 10 15 150
> 10 18 250
> 30 15 400
> 30 25 250
>
> The first two columns in each table makes the primary key.
>
> I want to calculate the sum of c_tot from c_table, sum of g_tot from
> g_table for each login_id.
>
> My result should look like the following one,
>
> login_id sum(c_tot) sum(g_tot)
> ----------- ----------- ------------
> 10 30 400
> 20 300 NULL
> 30 350 650
>
> Is there any simple way to achieve this?. If so how?
>
> Regards,
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Use in-line views:

column sum_c_tot format a9
column sum_g_tot format a9
select c.login_id,

       nvl(to_char(c.sum_c_tot),'NULL') sum_c_tot,
       nvl(to_char(g.sum_g_tot),'NULL') sum_g_tot
from (select login_id, sum(c_tot) sum_c_tot from c_table
      group by login_id) c, (select login_id, sum(g_tot) sum_g_tot
      from g_table group by login_id) g

where g.login_id (+) = c.login_id
order by c.login_id
/

The output is:

  LOGIN_ID SUM_C_TOT SUM_G_TOT
---------- --------- ---------

        10 30        400
        20 300       NULL
        30 350       650

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Jun 29 2000 - 00:00:00 CDT