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
.
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))
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With