Analytic Functions in SQL Server

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