Search code examples
sqlsql-serverwindow-functions

Calculate window function against another in same column

Hi I am using SQL server. I have a set of data and I am trying to use window functions to count the instances of something. I am then running the window function again and dividing it against a row count window function to try and get an average. However, I keep getting 0. Is it possible to run a window function against another in the same column?

COUNT(city) OVER (partition by state)/Count(*) over (partition by total)*100 AS AVG

is something like this possible? When i break it apart and run each individually it works, but when i combine them i get 0 in the column

Solution

  • This is because of integer arithmetic -- nothing to do with window functions. Write this as:

    COUNT(city) OVER (partition by state) * 100.0 / Count(*) over (partition by total) AS AVG

    The 100.0 puts a decimal point in the arithmetic. So, 1/2 is 0. But 1.0/2 is 0.5.