DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Query Help

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455

    Query Help

    I have following table
    Company Product Rec_Company
    ABC 101 NY
    EFG TX
    HIG 202 TX
    ABC 101 CA
    ABC 101 NY
    EFG NY
    HIJ 202 CA
    ABC 101 FL

    I want following result

    Company Product Rec_Company Rec_Count Product Rec_Company Count
    ABC 101 NY 2 NULL FL 1
    EFG
    HIG

    Let's take the result of first row
    Company ABC used product 101, and receive company was NY, and product was used 2 times, also ABC company has NULL product and recive company was FL, and so on with EFG and HIG company

    Notice that result in one row.

    Thanks in Advance

    For better viewing see notepad attachment
    Last edited by irehman; 12-21-2002 at 11:17 PM.

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Here is the attachment
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    U have X table and
    Compnay, product, Rec_company r columns???????

    Abhay

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Company ABC used product 101, and receive company was NY, and product was used 2 times, also ABC company has NULL product and recive company was FL, and so on with EFG and HIG company
    Needs more clarity in your example.. I have just pasted 'ABC's data.
    Code:
    company   Product           Rec_Company
    ABC          101              NY
    ABC          101              CA
    ABC          101              NY
    ABC          101              FL
    what is this 'CA'?? where is NULL product?? How do you define 'FL' is the receiver?? Is there any ID in between???

  5. #5
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    All the information coming from one table, no don't have ID, I just used NULL as an example, let's say ABC also had NULL value for Product then it should show up as "NULL" and count of NULL values

    For example, If ABC company has NULL under product and REC_COMPANY is "FL" then it should give me count 1 and REC_COMPANY "FL"

    Thanks

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Select
    x1.Company as Company,
    x1.Product as Product,
    x1.Rec_Company as Rec_Company,
    Count(x1.Rec_Company) as Rec_count,
    x2.Product as Product2,
    x2.Rec_Company as Rec_Company2,
    Count(x2.Rec_Company) as count
    From Tbl_name x1,Tbl_name x2
    where
    x1.company=x2.company and
    x1.product is not null and
    x1.Rec_Company is not null
    or x2.product is null


    Am not sure....try this

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by abhaysk
    Select
    x1.Company as Company,
    x1.Product as Product,
    x1.Rec_Company as Rec_Company,
    Count(x1.Rec_Company) as Rec_count,
    x2.Product as Product2,
    x2.Rec_Company as Rec_Company2,
    Count(x2.Rec_Company) as count
    From Tbl_name x1,Tbl_name x2
    where
    x1.company=x2.company and
    x1.product is not null and
    x1.Rec_Company is not null
    or x2.product is null


    Am not sure....try this
    Add Group By cluase

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    fyi count() is a group function and these dont deal with null values

  9. #9
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    May be this is one of possible decisions.
    U may use cast(multiset()) transformation
    Code:
    create or replace type r_counter is object
    ( u_name varchar2(30),
      o_cnt  number
    );
    /
    
    create or replace type t_counter as table of r_counter;
    /
    SQL> set lines 130
    set pages 9999
    col ot  format a25
    col cnt format a95 word
    
    select a.ot,
           cast(multiset( select b.owner, count(*)
                          from all_objects b
    		      where b.object_type = a.ot
    		            and
    			    b.owner in ('SYS','SYSTEM','TRAIN')
                          group by owner )
               as t_counter) cnt
    from ( select distinct object_type ot from all_objects) a;
    SQL> 
    OT			  CNT(U_NAME, O_CNT)
    ------------------------- -----------------------------------------------------------------------------------------------
    CLUSTER 		  T_COUNTER(R_COUNTER('TRAIN', 1))
    CONSUMER GROUP		  T_COUNTER(R_COUNTER('SYS', 3))
    FUNCTION		  T_COUNTER(R_COUNTER('SYS', 21), R_COUNTER('SYSTEM', 12), R_COUNTER('TRAIN', 12))
    INDEX			  T_COUNTER(R_COUNTER('SYS', 208), R_COUNTER('SYSTEM', 18), R_COUNTER('TRAIN', 109))
    LIBRARY 		  T_COUNTER(R_COUNTER('SYS', 19))
    PACKAGE 		  T_COUNTER(R_COUNTER('SYS', 122), R_COUNTER('SYSTEM', 1), R_COUNTER('TRAIN', 4))
    PACKAGE BODY		  T_COUNTER(R_COUNTER('SYS', 117), R_COUNTER('SYSTEM', 2), R_COUNTER('TRAIN', 4))
    PROCEDURE		  T_COUNTER(R_COUNTER('SYS', 10), R_COUNTER('SYSTEM', 12), R_COUNTER('TRAIN', 7))
    SEQUENCE		  T_COUNTER(R_COUNTER('SYS', 28), R_COUNTER('TRAIN', 10))
    SYNONYM 		  T_COUNTER(R_COUNTER('SYS', 6), R_COUNTER('SYSTEM', 8))
    TABLE			  T_COUNTER(R_COUNTER('SYS', 186), R_COUNTER('SYSTEM', 24), R_COUNTER('TRAIN', 85))
    TRIGGER 		  T_COUNTER(R_COUNTER('TRAIN', 12))
    TYPE			  T_COUNTER(R_COUNTER('SYS', 93), R_COUNTER('TRAIN', 10))
    VIEW			  T_COUNTER(R_COUNTER('SYS', 1144), R_COUNTER('SYSTEM', 24), R_COUNTER('TRAIN', 12))
    
    14 rows selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width