sqlteradatameanteradata-sql-assistant

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


I have table in Teradata SQL like below:

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

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


Solution

  • 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;