Home » SQL & PL/SQL » SQL & PL/SQL » count of tests (10)
| count of tests [message #645096] |
Thu, 26 November 2015 06:36  |
 |
pratik4891
Messages: 73
Registered: February 2011
|
Member |
|
|
Hello ,
Please find the ddls below
create table fact_test
(
order_no number ,
Orderable varchar2(10) ,
package varchar2(10) ,
test varchar2(10)
)
insert into fact_test (order_no , orderable , package , test ) values (101,'EEEVP','EEEV','EEEV') ;
insert into fact_test (order_no , orderable , package , test ) values (101,'EEEVP','G6PD','G6PD_RAW') ;
insert into fact_test (order_no , orderable , package , test ) values (101,'EEEVP','G6PD','G6PD_') ;
insert into fact_test (order_no , orderable , package , test ) values (101,'EEEVP','GPI','GPI_') ;
insert into fact_test (order_no , orderable , package , test ) values (101,'EEEVP','GPI','GPI_RAW') ;
insert into fact_test (order_no , orderable , package , test ) values (101,'EEEVP','PK','PK1') ;
insert into fact_test (order_no , orderable , package , test ) values (101,'EEEVP','PK','PK2') ;
insert into fact_test (order_no , orderable , package , test ) values (102,'G6PD','G6PD','G6PD_RAW') ;
insert into fact_test (order_no , orderable , package , test ) values (102,'G6PD','G6PD','G6PD_') ;
insert into fact_test (order_no , orderable , package , test ) values (103,'EEEV','EEEV','EEEV') ;
commit
Table values
order_no Orderable package test
101.00 EEEVP PK PK2
101.00 EEEVP GPI GPI_
101.00 EEEVP GPI GPI_RAW
101.00 EEEVP PK PK1
101.00 EEEVP EEEV EEEV
101.00 EEEVP G6PD G6PD_
101.00 EEEVP G6PD G6PD_RAW
102.00 G6PD G6PD G6PD_RAW
102.00 G6PD G6PD G6PD_
103.00 EEEV EEEV EEEV
requirement is to take the count of tests which can appear either in orderable or in package
please not same code can appear both in orderable and package but we have to count once
scenario 1
So the count of EEEVP will be 1 because the distinct count of eeevp combined both in orderable and package is 1
scenario 2
count of G6PD will be 2 because G6PD appears under order 101 as a package and under order 102 the count is 1 even though its appeared twice but distinct value is 1
Can anyone please help me out with the logic
|
|
|
|
|
|
| Re: count of tests [message #645098 is a reply to message #645096] |
Thu, 26 November 2015 07:37  |
 |
Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata
|
Junior Member |
|
|
As far as I understood.
Quote:
scenario 1
So the count of EEEVP will be 1 because the distinct count of eeevp combined both in orderable and package is 1
scenario 2
count of G6PD will be 2 because G6PD appears under order 101 as a package and under order 102 the count is 1 even though its appeared twice but distinct value is 1
This can be the query
select distinct nvl(a.orderable,b.package),nvl(count_order,0)+ nvl(count_package,0) count
from
(select order_no, count(distinct orderable) count_order, orderable
from fact_test
group by order_no, orderable) a
full outer join
(select order_no, count(distinct package) count_package, package
from fact_test
group by order_no, package) b
on a.orderable=b.package;
But can you clarify a little more as I cannot see any value as 'EEEVP' or 'G6PD' in the column test.
Just as Michel said.
[Updated on: Thu, 26 November 2015 07:38] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: count of tests [message #645131 is a reply to message #645129] |
Fri, 27 November 2015 01:35 |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
So, in short, you want the number of orders (i.e. distinct order_no) for each value found in orderable or package columns:
SQL> select decode(col, 1,orderable, 2,package) test,
2 count(distinct order_no) nb
3 from fact_test,
4 (select 1 col from dual union all select 2 from dual)
5 group by decode(col, 1,orderable, 2,package)
6 order by 1
7 /
TEST NB
---------- ----------
EEEV 2
EEEVP 1
G6PD 2
GPI 1
PK 1
For information, in 11g, you can use UNPIVOT clause:
SQL> select "Test", count(distinct order_no) "Nb"
2 from fact_test
3 unpivot ("Test" for
4 test_type in (orderable as 'Orderable', package as 'Package'))
5 group by "Test"
6 order by 1
7 /
Test Nb
---------- ----------
EEEV 2
EEEVP 1
G6PD 2
GPI 1
PK 1
[Updated on: Fri, 27 November 2015 01:35] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sun Jul 19 14:18:14 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
We're all a little confused now.