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