SQL Server CHISQN Function

Updated 2024-02-13 20:07:19.597000

Description

Use the aggregate function CHISQN to calculate the chi-square (χ²) 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.

CHISQN automatically calculates the expected results.

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

Syntax

SELECT [westclintech].[wct].[CHISQN] (
  <@expr1, nvarchar(4000),> 
 ,<@expr2, nvarchar(4000),> <@obs, float,>)

Arguments

@expr1

the name or number of the rows.

@expr2

the name or number of the columns.

Return Type

float

Remarks

CHISQN is designed for normalized tables.

CHISQN automatically calculates the expected values. If you want to enter the expected values use the CHISQN2 function.

CHISQN 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 CHISQN function to calculate the chi-square statistic.

SELECT wct.CHISQN(   sport,  --@expr1
                     locale, --@expr2
                     result  --@obs
                 ) as CHISQ
FROM
(
    VALUES
        ('Basketball', 'Rural', 28),
        ('Basketball', 'Suburban', 35),
        ('Basketball', 'Urban', 54),
        ('Baseball', 'Rural', 60),
        ('Baseball', 'Suburban', 43),
        ('Baseball', 'Urban', 35),
        ('Football', 'Rural', 52),
        ('Football', 'Suburban', 48),
        ('Football', 'Urban', 28)
) n (sport, locale, result);

This produces the following result.

What if the data had not been normalized? We could use that following SQL to take de-normalized data (the way it might be in a spreadsheet) and turn it into normalized data.

SELECT wct.CHISQN(n.sport, x.locale, x.result) as CHISQ
FROM
(
    VALUES
        ('Basketball', 28, 35, 54),
        ('Baseball', 60, 43, 35),
        ('Football', 52, 48, 28)
) n (sport, rural, suburban, urban)
    CROSS APPLY
(
    VALUES
        ('rural', rural),
        ('suburban', suburban),
        ('urban', urban)
) x (locale, result);

This produces the following result.

In this example, the de-normalized table contains another column, region. We will calculate the chi-square statistic for each region.

SELECT n.region,
       wct.CHISQN(n.sport, x.locale, x.result) as CHISQ
FROM
(
    VALUES
        ('Midwest', 'Baseball', 27, 38, 36),
        ('Midwest', 'Basketball', 46, 33, 31),
        ('Midwest', 'Football', 43, 29, 36),
        ('Northeast', 'Baseball', 39, 46, 42),
        ('Northeast', 'Basketball', 42, 34, 36),
        ('Northeast', 'Football', 35, 30, 41),
        ('Southeast', 'Baseball', 31, 36, 33),
        ('Southeast', 'Basketball', 32, 39, 34),
        ('Southeast', 'Football', 31, 24, 40),
        ('Southwest', 'Baseball', 44, 19, 34),
        ('Southwest', 'Basketball', 37, 42, 24),
        ('Southwest', 'Football', 41, 29, 36),
        ('West', 'Baseball', 43, 33, 30),
        ('West', 'Basketball', 44, 38, 34),
        ('West', 'Football', 30, 36, 30)
) n (region, sport, urban, rural, suburban)
    CROSS APPLY
(
    VALUES
        ('rural', rural),
        ('suburban', suburban),
        ('urban', urban)
) x (locale, result)
GROUP BY region;

This produces the following result.