Average of Minimums - Advanced SQL Engine - Teradata Database

The two example queries show the average of nested minimums. In the first query, the table is a PTI table and the second example shows a non-PTI table:

/*PTI Table*/
SELECT AVG(TEMP_MIN) FROM (
    SELECT MIN(TEMPERATURE) AS TEMP_MIN
    FROM OCEAN_BUOYS
    WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
    GROUP BY TIME (MINUTES(10) AND BUOYID)
) AS NESTED_MIN_TABLE;


/*Non-PTI Table*/
SELECT AVG(TEMP_MIN) FROM (
    SELECT MIN(TEMPERATURE) AS TEMP_MIN
    FROM OCEAN_BUOYS_NONPTI
    WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
    GROUP BY TIME (MINUTES(10) AND BUOYID)
    USING TIMECODE(TIMECODE)
) AS NESTED_MIN_TABLE;

The results get loaded into the derived table NESTED_MIN_TABLE. They are shown in ascending order, but this is not necessarily the expected order of the values in the derived table.

Result: The actual result of the minimum of results (which are nested in the NESTED_MIN_TABLE):

Average(TEMP_MIN)
-----------------
35