title: SQL syntax to SUM a Count column for calculating percentage distribution
body: <p>I'm using SQL Server 2014 and I have the following query which runs fine:</p>
  
  <pre><code>USE MyDatabase
  
  SELECT [Room Nights],
         COUNT([Room Nights]) AS 'Count of RN'
  FROM HOLDINGS2
  GROUP BY [Room Nights]
  </code></pre>
  
  <p>The output is as follows:</p>
  
  <pre><code>Room Nights      Count of RN
   1                 3
   4                 10
   5                 6
   7                 25
  </code></pre>
  
  <p>Now I want to show another column that gives me the percentage distribution of the <code>Count of RN</code>. Hence, my output will need to be like this:</p>
  
  <pre><code>Room Nights      Count of RN    % Distribution
       1                 3           6.8
       4                 10          22.7
       5                 6           13.6
       7                 25          56.8
  </code></pre>
  
  <p>I had a look at the following discussion to try to find out a solution:
  <a href="https://stackoverflow.com/questions/29170489/sql-percent-distribution-with-counted-values">percent distribution with counted values</a>.</p>
  
  <p>I came up with the following amendments to my existing code but it's not working! I am having only zeroes in the <code>% Distribution</code> column.</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>Basically, the <code>% Distribution</code> column should take the <code>Count of RN</code> and divide it by the TOTAL <code>Count of RN</code>.</p>