SQL syntax to SUM a Count column for calculating percentage distribution

Question

I'm using SQL Server 2014 and I have the following query which runs fine:

USE MyDatabase

SELECT [Room Nights],
       COUNT([Room Nights]) AS 'Count of RN'
FROM HOLDINGS2
GROUP BY [Room Nights]

The output is as follows:

Room Nights      Count of RN
 1                 3
 4                 10
 5                 6
 7                 25

Now I want to show another column that gives me the percentage distribution of the Count of RN. Hence, my output will need to be like this:

Room Nights      Count of RN    % Distribution
     1                 3           6.8
     4                 10          22.7
     5                 6           13.6
     7                 25          56.8

I had a look at the following discussion to try to find out a solution: percent distribution with counted values.

I came up with the following amendments to my existing code but it's not working! I am having only zeroes in the % Distribution column.

USE MyDatabase

SELECT [Room Nights],
       COUNT([Room Nights]) AS 'Count of RN',
       CAST(COUNT([Room Nights])/(SELECT COUNT([Room Nights])*100. FROM HOLDINGS2) AS DECIMAL (9,0)) AS '% Distribution'
FROM HOLDINGS2
GROUP BY [Room Nights]

Basically, the % Distribution column should take the Count of RN and divide it by the TOTAL Count of RN.

Answer 1

This would work:

select [Room Nights],
  count([Room Nights]) AS 'Count of RN',
  cast(
    (count([Room Nights])
    /
    (Select Count([Room Nights]) * 1.0 from HOLDINGS2) 
   ) * 100 as decimal(6,1)
  ) as '% Distribution'    
FROM HOLDINGS2
GROUP BY [Room Nights]

The * 1.0 in the subquery forces a floating point division, and the outer cast limits the precision.

Or, as you're using a modern version of MSSQL you could use window functions:

cast(count([Room Nights])/(sum(count([Room Nights])*1.0) over ()) * 100 as decimal(6,1))

Answer 2

Try:

DECLARE @t TABLE
    (
      [Room Nights] INT ,
      [Count of RN] INT
    )

INSERT  INTO @t
VALUES  ( 1, 3 ),
        ( 4, 10 ),
        ( 5, 6 ),
        ( 7, 25 )


SELECT  * ,
        ROUND([Count of RN] * 100.0
              / SUM([Count of RN]) OVER ( ORDER BY ( SELECT NULL ) ), 1) AS [Percent]
FROM    @t        

Output:

Room Nights Count of RN Percent
1           3           6.800000000000
4           10          22.700000000000
5           6           13.600000000000
7           25          56.800000000000

EDIT: I've missed that Count of RN is result of grouping query. Here is modified statement:

SELECT  [RN] ,
        COUNT(S) AS C ,
        CAST(COUNT(S) * 100.0 / SUM(COUNT(S)) OVER () AS DECIMAL(10, 1)) AS [Percent]
FROM    @t
GROUP BY [RN]