Analytic Functions in SQL Server I need to implement an ETL to move from Excel to SQL Server some information. In Excel, users have some analytical functions that we would like to have also in SQL Server (sigh...)

Does anybody know if I can find these functions in SQL Server?


RSQ (calculates the r-squared value)
LOGEST (calculates an exponential curve)
LOGNORM.INV (returns the inverse of the lognormal cumulative distribution function given specified parameters: mean/average and standard deviation)
STDEV (calculates the sample standard deviation)
SUMIFS (sums values based on multiple conditions)
COUNTIFS (counts values based on multiple conditions)
SUMPRODUCT (returns the sum of the products of corresponding ranges or arrays)
The following are natively available in SQL Server:


SUMIFS

-- SUM of a column or expression
SELECT SUM(IIF(so.[type] = 'S', so.[schema_id], 0)) AS [SumIFs]  -- 293
FROM   [master].[sys].[objects] so;

COUNTIFS

-- SUM as a counter, COUNT counting everything, and COUNT counting non-NULLs
SELECT SUM(IIF(so.[type] = 'S', 1, 0)) AS [SumIFs],  -- 74
       COUNT(IIF(so.[type] = 'S', 5, 0)) AS [CountIFsGotcha], -- 103
       COUNT(IIF(so.[type] = 'S', 1, NULL)) AS [CountIFsCorrect] -- 74
FROM   [master].[sys].[objects] so;

SUMPRODUCT

SELECT so.[type], SUM(CONVERT(BIGINT, so.[object_id]) * so.[schema_id])
FROM   [master].[sys].[objects] so
GROUP BY so.[type]
ORDER BY so.[type];

STDEV

SELECT STDEV(so.[schema_id]) AS [StandardDeviation]
FROM   [master].[sys].[objects] so;



Please note that the IIF statement -- introduced in SQL Server 2012 -- is just shorthand for: CASE WHEN expression THEN true_result ELSE not_true_result END. Example:

SELECT IIF('a' = NULL, 1, 2); -- 2


The following are not available natively in SQL Server, but can most likely be created as SQLCLR User-Defined Aggregates (UDAs).


RSQ
LOGEST
LOGNORM.INV