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
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???
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"
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
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
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.