当前位置: 动力学知识库 > 问答 > 编程问答 >

Count the Number of 'NA' in a single row - MySQL

问题描述:

If there is a row where there are more than or equal to 2 NA in two different columns, then I want to select that row and show all of its columns. Is there a SQL to do that?

Original Data:

 Overall_Score_ID Company_ID Operating_Margin_Score Negative_Earning_Surprise_Score Acquisition_Score TSR_Score Enterprise_Score

54 399473107 0.0 2.3770491803278686 5.666666666666666 NA 1.1475409836065573

116 92847A200 0.3305785123966942 3.0327868852459017 8.75 NA 2.0491803278688527

337 57164Y107 2.0689655172413794 8.85057471264368 7.558139534883721 NA 3.2558139534883725

415 007767106 NA 9.52 NA NA NA

462 36112J107 NA 1.28 NA NA NA

Output:

Overall_Score_ID Company_ID Operating_Margin_Score Negative_Earning_Surprise_Score Acquisition_Score TSR_Score Enterprise_Score

415 007767106 NA 9.52 NA NA NA

462 36112J107 NA 1.28 NA NA NA

Right now I only have a query to count the columns and show columns that are >= 2 and I know it's completely wrong, but I am thinking that I can modify it to the query that I want?

select * from overall_scores

group by overall_Score_id

having count(*) >=2;

网友答案:

This might work for you:

select *, 
((Operating_Margin_Score='NA') +
(Negative_Earning_Surprise_Score='NA') +
(Acquisition_Score='NA') +
(TSR_Score='NA') +
(Enterprise_Score='NA')) as total
from overall_scores having total >=2;
网友答案:

Try this query by changing the field names and table name. It may solve your problem

SELECT * FROM table1 WHERE (length(trim(concat(field1,field2,field3))) - length(replace(trim(concat(field1,field2,field3)),'NA',''))) / 2 >=2

you can find more examples like this on

www.csnotes32.com

分享给朋友:
您可能感兴趣的文章:
随机阅读: