Re: Complex qry

Hi:

Thanks for ur reply.

In article <8jg9bi$qkk$1_at_nnrp1.deja.com>,   ddf_dba_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 29 2000 - 00:00:00 CDT