TSQL calculate percentage

Question

I have a list of customers in a SQL Server database for a health club. The customers are listed with the date they joined and the number of times they have renewed I want to be able to calculate the percentage rate of customers that renewed. Any ideas on how I can do this? Thanks.

Solution 1

If there's a table subscriptions with only one row per user, that gets updated with a bit isrenewed:

 SELECT 
    SUM(SIGN(isrenewed)) * 100 / COUNT(*) 
    AS Percentage
    FROM subscriptions

Outputs

 Percentage
 ---------------
 66,666666

If you want to round the expression you can cast it as an int

 SELECT 
    CAST(
      SUM(SIGN(isrenewed)) * 100 / COUNT(*) 
      AS int)
    AS Percentage
    FROM subscriptions

Outputs

 Percentage
 ---------------
 66

If you had a table subscriptions with user-specific column userid where having two rows would mean the subscription was renewed:

SELECT 
  (SELECT TOP 1 COUNT(*) OVER() 
    FROM subscriptions
    GROUP BY userid
    having COUNT(*) > 1) 
  * 100 / COUNT(*) AS Percentage
  FROM subscriptions

Solution 2

Is this what you are looking for

Declare @t table(Id Int Identity,CustomerName Varchar(50),DOJ Datetime,IsRenewed bit)
Insert Into @t Values('Name1','1/1/2012',1),('Name2','10/1/2012',1),('Name3','10/2/2012',0)

Select PercentageCustomerRenewed = 
Cast((Count(*) * 100) / (Select Count(*) From @t ) AS Numeric(10,2))  
From @t 
where IsRenewed = 1

Result

PercentageCustomerRenewed
66.00