Difference between SUMSQ and SSQ2 aggregates

The INIT function for SUMSQ explicitly initializes the state; that is, the result. Because the SSQ2 aggregate does not include an INIT function, the ITER function must explicitly handle the case where the result is null.

The behavior of the SSQ2 aggregate is not exactly the same as that of the SUMSQ aggregate. You can use SSQ2 only with a column of the FLOAT data type unless you explicitly cast the column to FLOAT. In the following example, the first SELECT statement fails, but the other SELECT statements succeed:

CREATE TABLE trial (t INT);
  INSERT INTO trial VALUES (2);
  INSERT INTO trial VALUES (3);
SELECT ssq2(t) FROM trial;           -- fails
SELECT ssq2(t::float) FROM trial;    -- succeeds
SELECT sumsq(t) from trial;          -- succeeds

Because the INIT function was omitted from the declaration of SSQ2, the aggregate uses the data type of the aggregate argument as its state type. The ITER function expects a FLOAT data type. Thus, when the INIT function is omitted, the aggregate argument must be a FLOAT data type. For more about the state type, refer to Resolve the support functions.