Multiple Results for Time Series Aggregate Functions | Teradata Vantage - Example: Multiple Results Found for One or More Time Series Aggregate Functions - Analytics Database - Teradata Vantage

The example shows that more than one aggregate function has more than one result. Only one result per timebucket is returned. A warning message is also returned indicating that this has occurred.

CREATE TABLE ts_group_by_time_tbl(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER )
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 56, 44);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:10:24.000000', 44, 55, 43);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:10:24.333300', 44, 56, 44);
 
/*should give one result each and 1 warning*/

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, MODE(TEMPERATURE), MODE(SALINITY), COUNT(*)
  FROM ts_group_by_time_tbl
  WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 10:00:00' AND TIMESTAMP '2014-01-06 10:10:00'
GROUP BY TIME (MINUTES(10) AND BUOYID);

Result:

 *** Query completed. One row found. 6 columns returned.
 *** Warning: 4001 Multiple results found for one or more Time Series aggregate functions in this query, but only one result was returned. To get all results, resubmit this query with these aggregates isolated.
 *** Total elapsed time was 1 second.

TIMECODE_RANGE  ('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00')
GROUP BY TIME(MINUTES(10))            1
                    BUOYID           44
         MODE(TEMPERATURE)           43
            MODE(SALINITY)           55
                  Count(*)            2