I need your help to write a one SQL query. Following are the table name and column name.

Table Column Name
RA_CUSTOMER_TRX_ALL bill_to_customer_id
cust_trx_type_id

Now, my task is to get average number of transaction types per customer, minimum number of transaction type per customer, maximum number of transaction type.
For example, In following table How many TRX_type used per customer?

bill_to_customer_id cust_trx_type_id
1041 1020
1041 1021
1043 1020
1043 1021
1043 1023
1045 1023
1045 1027
1047 1020
1050 1027
1050 1021
---- ----
---- ----
---- ----
For above example, customer 1041 has 2 transaction type used, 1043 has 3 used, I am looking for all customer average how many transaction type used.
Average 1.25 transaction type used per customer
Minimum 1.0 transaction type used for customer
Maximum 4.0 transaction type used for customer

Hello,

Thanks for quick reply. This query calculates only transaction type's average but not by customer. What I am looking from this query is how many transaction type used per customer. Is there any other way?

Rajen

sorry

add bill_to_customer_id to the outer SELECT

then add GROUP BY bill_to_customer_id at the end

thanks again but result is not what I am looking for. Here is the result from query. Just sample,

Bill_To_custoner_id AVG_CUST_COUNT
1041 4
1043 2
1045 1
1047 2
--- ------

However, I am looking for average transaction type used per customer. On above example, total 4 customer and total transaction type 9 so 9 divide by 4 customer which will be average. 2.25. Then minimu will be 1 and maximum will be 4.