CHISQN2

Updated 2023-10-30 14:09:01.223000

Syntax

SELECT [westclintech].[wct].[CHISQ2] (
  <@obs, float,>
 ,<@exp, float)

Description

Use the aggregate function CHISQN2 to calculate the chi-square (χ2) statistic for normalized tables. This function calculates the chi-square statistic by finding the difference between each observed and theoretical frequency for each possible outcome, squaring them, dividing each by the theoretical frequency, and taking the sum of the results. A second important part of determining the test statistic is to define the degrees of freedom of the test: this is essentially the number of squares errors involving the observed frequencies adjusted for the effect of using some of those observations to define the expected frequencies.

CHISQN2 requires the expected results as input to the function.

The value of the chi-square statistic is:

\chi^2=\sum_{i=1}^r\sum_{j=1}^c\frac{(O_{i,j} - E_{i,j})^2}{E_{i,j}}

Where

{
    "columns": [
        {
            "field": "column 1",
            "maxWidth": 100
        },
        {
            "field": "column 2",
            "maxWidth": 225
        }
    ],
    "rows": [
        {
            "column 1": "r",
            "column 2": "is the number of columns"
        },
        {
            "column 1": "c",
            "column 2": "is the number of columns"
        },
        {
            "column 1": "O",
            "column 2": "is the Observed result"
        },
        {
            "column 1": "E",
            "column 2": "is the Expected result"
        }   ]
}

Arguments

@Exp

the observed value. @Exp is an expression of type float or of a type that can be implicitly converted to float.

@Obs

the observed value. @Obs is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

CHISQN2 is designed for normalized tables.

CHISQN2 requires expected values as input. If you want the expected values calculated automatically, use the CHISQN function.

CHISQN2 is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.

Examples

In this hypothetical situation, we want to determine if there is an association between population density and the preference for a sport from among baseball, football, and basketball. We will use the CHISQN2 function to calculate the chi-squared statistic.

SELECT wct.CHISQN2(   observed, --@obs

                      expected  --@exp

                  ) as CHISQ

FROM

(

    VALUES

        ('Basketball', 'Rural', 28, 42.77),

        ('Basketball', 'Suburban', 35, 38.49),

        ('Basketball', 'Urban', 54, 35.74),

        ('Baseball', 'Rural', 60, 50.44),

        ('Baseball', 'Suburban', 43, 45.4),

        ('Baseball', 'Urban', 35, 42.16),

        ('Football', 'Rural', 52, 46.79),

        ('Football', 'Suburban', 48, 42.11),

        ('Football', 'Urban', 28, 39.1)

) n (sport, locale, observed, expected);

This produces the following result

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"22.4562079299414"}]}