SQL Round Number with Over()

Question

I have a pretty basic sql query query that is doing a calculation on the total number of records returned from a grouped result set.

 SELECT   vco.OutcomeName,
          vco.VersionCompareOutcomeID,
          COUNT(t.OutcomeName) AS Total,
          ROUND(COUNT(*) * 100.0 / sum(count(*)), 1) over() AS Percentage
FROM map.VersionCompareOutcome AS vco
LEFT JOIN @temp AS t
ON vco.VersionCompareOutcomeID = t.VersionCompareOutcomeID
GROUP BY vco.OutcomeName, vco.VersionCompareOutcomeID

When I try and use the round function, I am getting the following error: The function 'ROUND' is not a valid windowing function, and cannot be used with the OVER clause.

Without the round function, I get the percentage but its not rounded like I am trying to achieve.

My desired outcome would be 2 decimal places: 87.95% for example.

Answer 1

You cannot use ROUND with OVER, only aggregates, ranking, and analytics functions can be used with over.

But, you can do your aggregation in a subquery or CTE, then do the rounding in the outer query:

WITH CTE
AS
(

    SELECT   vco.OutcomeName,
              vco.VersionCompareOutcomeID,
              COUNT(t.OutcomeName) AS Total
    FROM map.VersionCompareOutcome AS vco
    LEFT JOIN @temp AS t ON vco.VersionCompareOutcomeID = t.VersionCompareOutcomeID
    GROUP BY vco.OutcomeName, vco.VersionCompareOutcomeID
)
SELECT
  OutcomeName,
  VersionCompareOutcomeID,
  Total,
  CASE WHEN (SELECT COUNT(*) FROM @temp) = 0 THEN 0 ELSE ROUND(Total * 100.0 / (SELECT COUNT(*) FROM @temp), 1) END AS Percentage
FROM CTE