sqlaverageteradatateradata-sql-assistantteradatasql

How to calculate mean value per group in Teradata SQL?


have table in Teradata SQL like below:

SMS_ID | PRODUCT 
-------------------
11     | A
22     | A
33     | A
87     | B
89     | B
14     | C

My question is: How can I calculate in Teradata SQL mean number of SMS per PRODUCT ? As a result I need something like below:

AVG  | PRODUCT
-------
0.5  | A   -> because 3 / 6 = 0.5
0.33 | B   -> because 2 / 6 = 033
0.16 | C   -> because 1 / 6 = 0.16

Solution

  • You want fractions of the total count:

    SELECT
        product
       ,COUNT(*)  -- count per product
          / CAST(SUM(COUNT(*)) OVER () AS FLOAT) -- total count = sum of counts per procuct
    FROM yourTable
    GROUP BY PRODUCT