Teradata SQL count within subgroup

Database
CC
Fan

Teradata SQL count within subgroup

Hi all,

 

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.

1 REPLY
Teradata Employee

Re: Teradata SQL count within subgroup

Hi CC,

 

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.