Re: Query

On Sep 15, 11:09 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Mtek" <m..._at_mtekusa.com> schreef in berichtnews:0eb27f6d-e7b4-4c62-8c54-1c1dfdff77fd_at_y38g2000hsy.googlegroups.com...
> On Sep 15, 8:41 am, Carlos <miotromailcar..._at_netscape.net> wrote:
>
>
>
>
>
> > On 15 sep, 15:31, Mtek <m..._at_mtekusa.com> wrote:
>
> > > Hi,
>
> > > I was wondering if I can display data like this:
>
> > > Original Data
> > > 01-JUN-08 Type1 Add 4308
> > > 01-JUN-08 Type1 Delete 4687
> > > 01-OCT-08 Type2 Delete 5545
> > > 01-OCT-08 Type2 Add 11801
>
> > > New Data
> > > 01-JUN-08 Type1 Add 4308 Delete 4687
> > > 01-OCT-08 Type2 Add 11801 Delete 5545
>
> > > I want all related records on the same line......
>
> > > Much thanks
>
> > Google for 'PIVOT TABLE'
>
> > HTH
>
> > Cheers.
>
> > Carlos.- Hide quoted text -
>
> > - Show quoted text -
>
> Ok, I find this example on Tom Kyte's page:
>
> select job,
> max( decode( deptno, 10, cnt, null ) ) dept_10,
> max( decode( deptno, 20, cnt, null ) ) dept_20,
> max( decode( deptno, 30, cnt, null ) ) dept_30,
> max( decode( deptno, 40, cnt, null ) ) dept_40
> from ( select job, deptno, count(*) cnt
>          from emp
>         group by job, deptno )
> group by job;
>
> Mine is almost identical, but I get an error:
>
> select TRUNC(action_date,'MM'),
>   max( decode( email_product_id, 'PPM', cnt, null ) ) 'Product1',
>   max( decode( email_product_id, 'HL',  cnt, null ) ) 'Product2',
>   max( decode( email_product_id, 'ZI',  cnt, null ) ) 'Product3',
>   max( decode( email_product_id, 'PP',  cnt, null ) ) 'Product4',
>   max( decode( email_product_id, 'PP1', cnt, null ) ) 'Product5'
> from ( select TRUNC(action_date,'MM'), email_product_id, count(*) cnt
>          from email_product_hist
>         group by email_product_id )
> group by email_product_id;
>
> ERROR at line 2:
> ORA-00923: FROM keyword not found where expected
>
> I cannot get this one......
>
> =============================================
> Don't put your column aliases in single quotes .... 'Product1' etc
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Thanks! I almost have what I want......but I have one question. I have 2 columns like this:

       sum(decode(action,'A',1, 0 )) "Add",
       sum(decode(action,'D',1, 0)) "Delete",

Now, I want to find the difference of the Add column - the Delete column.........everything comes up as 0...... Received on Mon Sep 15 2008 - 11:28:00 CDT