2

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

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
1

RSQ - doesn't exist

STDEV in SQL

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, privacy policy and cookie policy

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