Operand data type char is invalid for avg operator

Question

I was asked to get the frequency of transactions on June 2018 using credit card ( trn_type = 'C')

My DataFrame is as follows:

Trn_id  ID  merchant_id trn_date    trn_amt trn_type
1   1111    33  2019-01-02  4000    C
2   1111    11  2019-01-03  200 R
3   2222    44  2018-03-25  400 P
4   2222    33  2018-03-25  20000   P
5   4444    11  2018-10-15  5000    R
6   4444    22  2018-11-13  230 R

Obviously the answer is going to be 0, but this was the question. So I used the following query:

SELECT AVG(ISNULL(CAST(trn_type as integer),0)) as 'Frequency'
FROM Trn 
WHERE YEAR(trn_date) = '2018' and MONTH(trn_date) = '06' and trn_type = 'C'

And This is the answer I've gotten:

Frequency
NULL

Can anybody please help? many 10x's

Answer 1

Are you overcomplicating things? Frequency means how many times something occurs in the data set so if you want frequency of transaction types, this should work:

SELECT trn_type, count(trn_type) as 'Frequency' FROM Trn 
WHERE YEAR(trn_date) = '2018' and MONTH(trn_date) = '06' 
GROUP BY trn_type

--If you want percentage of 'C' relative to all records in the table:

SELECT CAST(ROUND(100. * (COUNT(trn_type)) /
(SELECT COUNT(*) FROM Trn ), 0) AS INT) FROM Trn 
WHERE YEAR(trn_date) = '2018' and MONTH(trn_date) = '06' and  trn_type='C'