CHISQ

Updated 2024-02-13 20:06:00.547000

Syntax

SELECT [westclintech].[wct].[CHISQ] (
  <@Actual_range_TableName, nvarchar(4000),>
 ,<@ColumnNames, nvarchar(4000),>
 ,<@GroupedColumnName, nvarchar(4000),>
 ,<@GroupedColumnValue, sql_variant,>)

Description

Use the scalar function CHISQ to calculate the chi-square (χ2) statistic. 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.

CHISQ 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

{
    "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

@GroupedColumnName

the name, as text, of the column in the table or view specified by @Actual_range_TableName which will be used for grouping the results.

@Actual_range_TableName

the name, as text, of the table or view that contains the observed results to be used in the calculation.

@ColumnNames

the names, as text, of the columns in the de-normalized table or view specified by @Actual_range_TableName that contains the observed results to be used in the calculation.

@GroupedColumnValue

the column value to do the grouping on.

Return Type

float

Remarks

Use CHISQ for de-normalized tables. Use the CHISQN function for normalized tables,.

CHISQ automatically calculates the expected values. If you want to enter the expected values use the CHISQ2 function.

For queries that are more complex, consider using the CHISQ_q function.

No GROUP BY is required for this function even though it produces aggregated results.

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 CHISQ function to calculate the chi-squared statistic.

CREATE TABLE #chi

(

    [Sport] [varchar](20) NOT NULL,

    [Rural] [float] NOT NULL,

    [Suburban] [float] NOT NULL,

    [Urban] [float] NOT NULL

);

INSERT INTO #CHI

VALUES

('Basketball', 28, 35, 54);

INSERT INTO #CHI

VALUES

('Baseball', 60, 43, 35);

INSERT INTO #CHI

VALUES

('Football', 52, 48, 28);

SELECT wct.CHISQ('#chi', 'Rural,Suburban,Urban', '', NULL);

This produces the following result.

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