I am using TD v15. I have a table as below - each row is a single record, I want to perform Count in the following way:
In Question Column: I have 4 'A', 5 'B', 3 'C' and 2 'D'. Select top 2 from them, which are A & B. Group the rest Questions as 'OtherQ' - Put them in Result Question Column.
In Change Column, I have 2 'AA', 3 'AB', 2'AC', 2 'AD', 4 'AE' and 2 'AG', select top 2, which are AE & AB, group the rest Change as 'Other' - Put them in Result Change Column.
Then, count accordingly...
Question Result Change A Pass AG A Pass AE A Pass AA A Pass AB B Pass AC B Pass AG B Pass AB B Pass AE B Pass AD B Pass AA C Pass AB C Pass AC C Pass AD D Pass AE D Pass AE A Fail Null A Fail Null C Fail Null E Fail Null B Fail Null
This is the desired result, it counts on top 2 questions (A&B) and OtherQ with Top 2 changes (AE&AB) and other Changes, also, it counts Pass&Fail for A&B and OtherQ.
The sum of Count is 20, this should match the 20 individual row in the table above.
Question Result Change Count A Pass AE 1 A Pass AB 1 A Pass Other 2 B Pass AE 1 B Pass AB 1 B Pass Other 4 OtherQ Pass AE 2 OtherQ Pass AB 1 OtherQ Pass Other 2 A Fail Null 2 B Fail Null 1 OtherQ Fail Null 2
Could you please kindly help? It's very large data table, needs the code to be efficient. Many thank for your time and help in advance.
I assumed you counted Questions & Changes only on Pass results.
You can still go for two aggregates then some joinings :
with cte_QRCTable_Question (Question) as ( select top 2 Question from QRCTable where Results = 'Pass' group by Question order by count(*) desc ) , cte_QRCTable_Change (Change) as ( select top 2 Change from QRCTable where Results = 'Pass' group by Change order by count(*) desc ) select coalesce(cqq.Question, 'OtherQ') as Question , qrc.Results , coalesce(cqc.Change, 'Other') as Change , count(*) from QRCTable as qrc left join cte_QRCTable_Question as cqq on cqq.Question = qrc.Question left join cte_QRCTable_Change as cqc on cqc.Change = qrc.Change group by cqq.Question, qrc.Results, cqc.Change order by qrc.Results desc, 1, 3 ;
You still have to establish a rule in the case of three questions having the same number of answers, how to choose two amongst them.