how to count col1 and col2 as One, where col1 is not duplicate and col2 = fail
-
hi All,
I really dont know how to write my query so that i get what i want.
example table data :
Col1 Col2
aa1 Fail
aa2 Fail
aa3 Ok
aa4 Fail
aa4 Ok
aa5 Fail
aa5 Ok
example query: not correct just to show what i want to get.
select count(distinct(Col1) and col2='Fail') as True_Fail From TableName
first i want to select the distinct in Col1 then Col2 must have Fail value. because i have also distinct in col1 and col2=Ok i don't want to count that.
Output:
True_Fail
2Please help me modify my code.
- Ladia_emil - Thursday, June 28, 2018 10:09 PM
How about
select count(distinct(Col1)) as True_Fail
from TableName
where col2='Fail' -
I can't use where because i have another data to query,
Total_input | Total_Ok | Total_Fail | True_Fail
5 | 2 | 4 | ??I already got the first 3 columns , but the True_Fail Column I dont know what to add in my query see below:
select count(distinct(Col1))as Total_Input,count(case when Col2='OK' then 'OK' end)as Total_OK,
count(case when Col2 ='FAIL' then 'FAIL' end)as Total_Fail from MyTableName where line_no='H5'and
TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and Col1 LIKE 'aa%'
group by line_no - Ladia_emil - Thursday, June 28, 2018 10:25 PM
Are you looking for pairs of records for Col1 with at least one "Fail" and one "Ok" record
Eg: In the first example you mentioned the expected output is
True_Fail=2.
Is it because we have for
Col1 Col2
aa1 Fail
aa2 Fail
aa3 Ok
aa4 Fail
aa4 Ok
aa5 Fail2 records?.
And supposing the record set was as follows
Col1 Col2
aa1 Fail
aa2 Fail
aa3 Ok
aa3 Fail
aa4 Fail
aa4 Ok
aa5 FailIs the expected output for True_fail = 4 (2 for aa3 and 2 for aa4?)
- george_at_sql - Thursday, June 28, 2018 11:48 PM
hi Sir,
Sorry i updated my first sample to have output True_Fail=2, hope you get what i mean.
first I am looking for Col1 that has 1 record only. and has record in Col2 = Fail. it means that records are True Fail.
True_Fail mean records that has no Col2=Ok,so from your second sample the expected Output must be True_Fail =3 (from aa1,aa2 & aa5) all has one records in Col1 and Col2 = Fail only.
is that possible Sir? - Ladia_emil - Friday, June 29, 2018 12:02 AM
Please check if this works out for you.
I added a new block called "data" which will get you the count of col1 with only failures. After that i am joining that result set with your original query and selecting the count_of_only_failures.By the way this looks like its an Oracle query, so please check it against a Oracle database.
with data
as (
select count(x.col1) as cnt_only_failures /* Get all the distinct col1 which have only Fail values*/
from ( select count(case when Col2='Fail' then 1 end) as count_only_fail
,count(case when Col2=<>'Fail' then 1 end) as count_only_notfail
,col1
from MyTableName
where line_no='H5'
and TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and Col1 LIKE 'aa%'
group by col1
having count(case when Col2='Fail' then 1 end)=1 /* Both these conditions ensure we have only 1 record for failure and no record for OK*/
and count(case when Col2=<>'Fail' then 1 end)=0
)x
)
select count(distinct(Col1)) as Total_Input
,count(case when Col2='OK' then 'OK' end) as Total_OK
,count(case when Col2 ='FAIL' then 'FAIL' end) as Total_Fail
,max(b.cnt_only_failures) as Only_Fails /* Max/Min can used, It shouldnt matter since there is only one row in data*/
from MyTableName a
join data b /*The output of data would give you only 1 record so cartesian join should not change the results.*/
on 1=1
where line_no='H5'
and TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and Col1 LIKE 'aa%'
group by line_no - Ladia_emil - Friday, June 29, 2018 12:02 AM
I realised i can simplify my earlier solution further
select count(col1) as total_input
,count(x.count_ok) as total_ok
,count(x.count_fail) as total_fail
,count(case when x.total_count=x.count_fail then 1 end) as true_fail
from ( select line_no
,col1
,count(case when Col2='OK' then 'OK' end) as count_ok
,count(case when Col2 ='FAIL' then 'FAIL' end) as count_fail
,count(*) as total_count
from MyTableName a
where line_no='H5'
and TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and Col1 LIKE 'aa%'
group by line_no
,col1
)x -
Hi Sir,
I don't understand the code below, 'Join data b' ????
I am only working in 1 Table MyTableName, where should i join it?
sorry i'm a newbie.
from MyTableName a
join data b /*The output of data would give you only 1 record so cartesian join should not change the results.*/
on 1=1 - Ladia_emil - Friday, June 29, 2018 12:53 AM
Please check the following instead
select count(col1) as total_input
,count(x.count_ok) as total_ok
,count(x.count_fail) as total_fail
,count(case when x.total_count=x.count_fail then 1 end) as true_fail
from ( select line_no
,col1
,count(case when Col2='OK' then 'OK' end) as count_ok
,count(case when Col2 ='FAIL' then 'FAIL' end) as count_fail
,count(*) as total_count
from MyTableName a
where line_no='H5'
and TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmddhh24miss')
and Col1 LIKE 'aa%'
group by line_no
,col1
)x -
Hi Sir,
Can i change my query to count how many duplicate records in my col1 ?
example:
Col1 Col2
aa1 Fail
aa2 Fail
aa3 Ok
aa4 Fail
aa4 Ok
aa5 Fail
aa5 Ok
Output : Total_Retest = 2 because aa4 & aa5 has duplicate data. and if possible can you add it to my previous code.
select count(distinct(Col1))as Total_Input,count(case when Col2='OK' then 'OK' end)as Total_OK,
count(case when Col2 ='FAIL' then 'FAIL' end)as Total_Fail,
Count(Col1 >1 )as RE_TEST /* Please modify this area , I want to count records in Col1 that has duplicate or greater than 1 records*/
from MyTableName where line_no='H5'and
TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and Col1 LIKE 'aa%'
group by line_noso my final output is :
Total_input | Total_Ok | Total_Fail | RETEST
5 | 2 | 4 | 2 - Ladia_emil - Friday, June 29, 2018 2:31 AM
what is the criteria to check if its a "duplicate". How did you arrive at aa5 and aa4 in the above example as eligible for ReTest?
Does a duplicate mean the col1 must have exactly one Fail record and one Ok record? -
what is the criteria to check if its a "duplicate". How did you arrive at aa5 and aa4 in the above example as eligible for ReTest?
COL1 ARE RECORDS FOR SERIAL NUMBER, SO IF THE SERIAL NUMBER IS DUPLICATES IT MEANS IT HAS COL2 AS A TEST_RESULT HAS FIRST TEST FAIL AND SECOND TEST OK,
Does a duplicate mean the col1 must have exactly one Fail record and one Ok record?
COL1 WILL HAVE THE SAME SERIAL NUMBER AND COL2 WILL HAVE FAIL AND OK RECORDS.so i want to count that records as one. EXAMPLE
COL1/SERIAL NO || COL2 TEST_RESULT
AA1 **************** ||**************** FAIL
AA1 **************** || **************** OK
AA2 **************** || **************** FAIL
AA2 **************** || **************** OKOUTPUT RE-TEST = 2 , IT HAS 4 RECORDS BUT ACTUALLY THE SERIAL NUMBER IS ONLY 2 . SO IT MUST BE 2 ONLY.
- Ladia_emil - Friday, June 29, 2018 8:10 PM
So you are basically looking for pair of records for a Serial No with one Fail and one Ok(And I assume that a retest is taken only if there is a fail ?)
And if there is a retest would the OK need to be counted under Total_Ok as well?
select count(col1) as total_input
,count(x.count_ok) as total_ok
,count(x.count_fail) as total_fail
,count(case when x.total_count=x.count_fail then 1 end) as true_fail
,count(x.retest_flag) as retest_count
from ( select line_no
,col1
,count(case when Col2='OK' then 'OK' end) as count_ok
,count(case when Col2 ='FAIL' then 'FAIL' end) as count_fail
,count(*) as total_count
,case when count(case when Col2='OK' then 'OK' end) = count(case when Col2 ='FAIL' then 'FAIL' end)
AND count(case when Col2='OK' then 'OK' end)= 1
then
1
end as retest_flag
from MyTableName a
where line_no='H5'
and TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and Col1 LIKE 'AA%'
group by line_no
,col1
)x - george_at_sql - Friday, June 29, 2018 10:15 PM
I follow your code but give me different output, below is the result of your query, how come the first 3 columns has the same value ??
when i used my code below, without True fail and RETEST yet because i use manual to get that .
select line_no,count(distinct(MO_SN))as Total_Input,count(case when Def_Item='OK' then 'OK' end)as Total_OK,
count(case when Def_Item ='FAIL' then 'FAIL' end)as Total_Fail,STATION from dqc342 where line_no='H5'and
TEST_DTTM BETWEEN TO_DATE('20180530','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and MO_SN LIKE 'S46M5%' AND STATION='PCA ICT'
group by line_no,STATIONBy the way,
TOTAL_INPUT = count all Col1 without duplicate. count(distinct(col1)as Total_Input
TOTAL_OK = count all OK in Col2 ,
TOTAL_FAIL= count all FAIL in Col2
my problem is how to get below,
RETEST= COL1 =2 OR DUPLICATE COL2 = OK & FAIL
TRUE FAIL= COL1 =1 NO DUPLICATE COL2 = FAIL ONLY.
and please if possible add it to my previous code.
thank you in advance. - BONITO - Saturday, June 30, 2018 1:50 AMgeorge_at_sql - Friday, June 29, 2018 10:15 PM
I follow your code but give me different output, below is the result of your query, how come the first 3 columns has the same value ??
when i used my code below, without True fail and RETEST yet because i use manual to get that .
select line_no,count(distinct(MO_SN))as Total_Input,count(case when Def_Item='OK' then 'OK' end)as Total_OK,
count(case when Def_Item ='FAIL' then 'FAIL' end)as Total_Fail,STATION from dqc342 where line_no='H5'and
TEST_DTTM BETWEEN TO_DATE('20180530','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and MO_SN LIKE 'S46M5%' AND STATION='PCA ICT'
group by line_no,STATIONBy the way,
TOTAL_INPUT = count all Col1 without duplicate. count(distinct(col1)as Total_Input
TOTAL_OK = count all OK in Col2 ,
TOTAL_FAIL= count all FAIL in Col2
my problem is how to get below,
RETEST= COL1 =2 OR DUPLICATE COL2 = OK & FAIL
TRUE FAIL= COL1 =1 NO DUPLICATE COL2 = FAIL ONLY.
and please if possible add it to my previous code.
thank you in advance.Have a look at the following query, the columns have been named as per what they represent.
select count(col1) as as count_col1
,count(case when x.count_ok=1 then 1 end) as total_count_ok
,count(case when x.count_fail=1 then 1 end) as total_count_fail
,count(case when x.total_count=x.count_ok then 1 end) as count_only_ok
,count(case when x.total_count=x.count_fail then 1 end) as count_only_fail
,count(x.retest_flag) as count_one_fail_one_ok
from ( select line_no
,col1
,count(case when Col2='OK' then 'OK' end) as count_ok
,count(case when Col2 ='FAIL' then 'FAIL' end) as count_fail
,count(*) as total_count
,case when count(case when Col2='OK' then 'OK' end) = count(case when Col2 ='FAIL' then 'FAIL' end)
AND count(case when Col2='OK' then 'OK' end)= 1
then
1
end as retest_flag
from MyTableName a
where line_no='H5'
and TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
and Col1 LIKE 'AA%'
group by line_no
,col1
)x
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply