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)

2 Answers 2


The following are natively available in SQL Server:


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

    -- 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;

    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];

    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

RSQ - doesn't exist


SUMIFS - use SUM and where condition
Count if - use COUNT and where condition
SUMPRODUCT - use SUM and product

Other mathematical functions are in here: https://msdn.microsoft.com/en-us/library/ms177516.aspx


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.