Aggregates and Literal Expressions in the Select List - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantage™

Literal expressions in the select list may optionally appear in the GROUP BY clause. For example, the following statement uses an aggregate function and a literal expression in the select list, and does not use a GROUP BY clause:

SELECT COUNT(*), 
SUBSTRING( CAST( CURRENT_TIME(0) AS CHAR(14) ) FROM 1 FOR 8 )
FROM Sales_Table;

The results of such statements when the table has no rows depends on the type of literal expression.

IF the literal expression … THEN the result of the literal expression in the query result is …
does not contain a column reference

is a non-deterministic function, such as RANDOM

the value of the literal expression.

Functions such as RANDOM are computed in the immediate retrieve step of the request instead of in the aggregation step.

Here is an example:

SELECT COUNT(*),
SUBSTRING(CAST(CURRENT_TIME(0) AS CHAR(14))
FROM 1 FOR 8)
FROM Sales_Table;

Result:

Count(*) Substring(Current Time(0) From 1 For 8)
-------- ---------------------------------------
       0 09:01:43
contains a column reference

is a UDF

NULL.

Here is an example:

SELECT COUNT(*), UDF_CALC(1,2) 
FROM Sales_Table;

Result:

   Count(*) UDF_CALC(1,2)
----------- -------------
          0             ?