How to calculate mean number of something per client in Teradata SQL?

Question

I have table in Teradata SQL like below:

SMS_ID | CLIENT_ID 
-------------------
11     | 123   
22     | 123
33     | 123
87     | 456
89     | 456
14     | 888

• Column "SMS_ID" presents ID of SMS sent do client
• Column "CLINET_ID" presents ID of client which received SMS

My question is: How can I calculate in Teradata SQL mean number of SMS per CLIENT ?

If I calculate correctly, mean number of SMS per client im my example is 2 because (3+2+1) / 3 = 2

Answer 1

First aggregate and find the counts for each client, then subquery the and take the average of the entire table.

SELECT AVG(cnt)
FROM
(
    SELECT COUNT(*) AS cnt
    FROM yourTable
    GROUP BY CLIENT_ID
) t;