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.

score 4 · Accepted Answer · 2017-10-16T18:47:45.613

4

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

edited Oct 16 '17 at 18:47

answered Oct 16 '17 at 17:59

I am trying to integrate this but I am getting an error `Divide by zero error encountered.`. There could be a situation where my total is `0` so I would need to show `0%` as the Percentage. Is this something I can account for in your example? – SBB Oct 16 '17 at 18:11
Strange, the results keep saying that the records with numbers are `100%` – SBB Oct 16 '17 at 18:16
@SBB - Sorry it was my fault, try the updated query again :D – Oct 16 '17 at 18:18
I will need to keep looking into this, the results are coming up as 100% each time for some reason. Possibly miscommunication on what my query is doing. – SBB Oct 16 '17 at 18:28
@SBB Can you please edit your question with some sample data from both tables? – Oct 16 '17 at 18:30
The temp table `@temp` has all of the records stored in it that I am working with. I group that data by `versionOutcomeID` & `OutcomeName` to get me the total number of records for each outcome type. From that number, I need to determine what percentage is based on the whole total. – SBB Oct 16 '17 at 18:38
@SBB Can you show a screenshot of the results of the inner query of the `CTE`?? – Oct 16 '17 at 18:39
https://image.ibb.co/ipkid6/Screen_Shot_2017_10_16_at_11_42_53_AM.png - The count total is also wrong on the ones with `0` results as it should be `0` for the count total. – SBB Oct 16 '17 at 18:44
@SBB The problem is that `COUNT(t.OutcomeName)` is always the same as `COUNT(*)` which makes sense as you are grouping by `OutcomeName`. – Oct 16 '17 at 18:46

SQL Round Number with Over()

1 Answers1