sql server - SQL Percentage of Occurrences

Question

I'm working on some SQL code as part of my University work. The data is factitious just to be clear. I'm trying to count the occurances of 1 & 0 in the SQL table Fact_Stream, this is stored in the Free_Stream column/attribute as a Boolean/bit value.

As calculations cant be made on bit values (at least in the way I'm trying) I've converted the value to an integer -- Just to be clear on that. The table contains information on a streaming companies streams, a 1 indicates the stream was free of charge, a 0 indicates the stream was paid for. My code:

SELECT Fact_Stream.Free_Stream, ((CAST(Free_Stream AS INT)) / COUNT(*) * 100) As 'Percentage of Streams'
FROM Fact_Stream
GROUP BY Free_Stream

The result/output is nearly where I want it to be, but it doesn't display the percentage correctly.

Output:

Using MS SQL Management Studio | MS SQL Server 2012 (I believe)

Answer 1

The percentage should be based on all rows, so you need to divide the count per 1/0 by a count of all rows. The easiest way to get this is utilizing a Windowed Aggregate Function:

SELECT Fact_Stream.Free_Stream,
   100.0 * COUNT(*)        -- count per bit
   / SUM(COUNT(*)) OVER () -- sum of those counts = count of all rows
   As "Percentage of Streams"
FROM Fact_Stream
GROUP BY Free_Stream