REGR_COUNT - Teradata Database

REGR_COUNT

Purpose  

Returns the count of all non-null data pairs of the dependent and independent variable arguments.

Syntax  

where:

Syntax element …	Specifies …
dependent_variable_expression	the dependent variable for the regression. A dependent variable is something that is measured in response to a treatment. The expression cannot contain any ordered analytical or aggregate functions.
independent_variable_expression	the independent variable for the regression. An independent variable is a treatment: something that is varied under your control to test the behavior of another variable. The expression cannot contain any ordered analytical or aggregate functions.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Setting Up Axes for Plotting

If you export the data for plotting, define the y-axis (ordinate) as the dependent variable and the x-axis (abscissa) as the independent variable.

Combination With Other Functions

REGR_COUNT can be combined with ordered analytical functions in a SELECT list, QUALIFY clause, or ORDER BY clause. For more information on ordered analytical functions, see Chapter 22: “Ordered Analytical / Window Aggregate Functions.”

REGR_COUNT cannot be combined with aggregate functions within the same SELECT list, QUALIFY clause, or ORDER BY clause.

Result Type and Attributes

The following table lists the data type for the result of REGR_COUNT(y,x).

Mode	Data Type
ANSI	If MaxDecimal in DBSControl is… 0 or 15, then the result type is DECIMAL(15,0). 18, then the result type is DECIMAL(18,0). 38, then the result type is DECIMAL(38,0).
Teradata	INTEGER

The result type of REGR_COUNT is consistent with the result type of COUNT for ANSI transaction mode and Teradata transaction mode.

When in Teradata mode, if the result of REGR_COUNT overflows and reports an error, you can cast the result to another data type, as illustrated by the following example.

   SELECT CAST(REGR_COUNT(weight,height) AS BIGINT)

   FROM regrtbl;

Here are default formats and titles for the result of REGR_COUNT.

If operand y is numeric or character, the format is:

For ANSI mode, if MaxDecimal in DBSControl is:

0 or 15, the format is -(15)9

18, the format is -(18)9

38, the format is -(38)9

For Teradata mode, the format is the default format for INTEGER

If operand y is UDT, the format is the format for the data type to which the UDT is implicitly cast.

For information on data type default formats, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

Support for UDTs

By default, Teradata Database performs implicit type conversion on UDT arguments that have implicit casts that cast between the UDTs and any of the following predefined types:

Numeric

Character

DATE

Interval

To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

Implicit type conversion of UDTs for system operators and functions, including REGR_COUNT, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”

REGR_COUNT Window Function

For the REGR_COUNT window function that performs a group, cumulative, or moving computation, see “Window Aggregate Functions” on page 984.

Example  

This example is based the following regrtbl data. Nulls are indicated by the QUESTION MARK character.

c1	height	weight
1	60	84
2	62	95
3	64	140
4	66	155
5	68	119
6	70	175
7	72	145
8	74	197
9	76	150
10	76	?
11	?	150
12	?	?

The following SELECT statement returns the number of rows in regrtbl where neither height nor weight is null.

   SELECT REG_COUNT(weight,height) 

   FROM regrtbl;

Here is the result:

   Regr_Count(weight,height)

   -------------------------

                           9