Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server chi-squared statistic


CHISQ_q

Updated: 6 August 2010

Use CHISQ_q 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_q automatically calculates the expected results.
 
The value of the chi-square statistic is:


 
Where
                r              is the number of rows
                c              is the number of columns
                O             is the Observed result
                E              is the Expected result
 
Syntax
SELECT [wctStatistics].[wct].[CHISQ_q] (
   <@Actual_range_RangeQuery, nvarchar(4000),>)
Arguments
@Actual_range_RangeQuery
the select statement, as text, used to determine the values to be used in the calculation.
Return Types
float
Remarks
·         Use CHISQ2_q for de-normalized tables. Use the CHISQN2_q function for normalized tables.
·         CHISQ2_q requires the expected values as input. If you want the expected values calculated automatically use the CHISQ_q function.
·         For queries that are less complex, consider using the CHISQ2 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_q 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_q(
'SELECT Rural
,Suburban
,Urban
FROM #chi')
 
This produces the following result
 
----------------------
22.451703426585
 
(1 row(s) affected)
We could have also invoked the function by passing the data directly to it.
SELECT wct.CHISQ_q(
'SELECT 28,35,54 UNION ALL
 SELECT 60,43,35 UNION ALL
 SELECT 52,48,28')
This produces the following result
 
----------------------
22.451703426585
 

(1 row(s) affected)



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service