-1

have table in Teradata SQL like below:

SMS_ID | PRODUCT 
-------------------
11     | A
22     | A
33     | A
87     | B
89     | B
14     | C
  • Column "SMS_ID" presents ID of SMS sent do client
  • Column "PRODUCT" presents ID of product which was a subject of SMS

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
dingaro
  • 2,156
  • 9
  • 29
  • Does this answer your question? [How to calculate mean number of something per client in Teradata SQL?](https://stackoverflow.com/questions/70739424/how-to-calculate-mean-number-of-something-per-client-in-teradata-sql) – Nathan_Sav Jan 17 '22 at 15:58
  • Yes, but it question does not answer on my current, new problem :) – dingaro Jan 17 '22 at 16:01

1 Answers1

1

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
dnoeth
  • 59,503
  • 4
  • 39
  • 56