How to accurately figure percentage of a finite total
I’m in the process of creating a report that will tell end users what percentage of a gridview (the total number of records is a finite number) has been completed in a given month. I have a gridview with records that I’ve imported and users have to go into each record and update a couple of fields. I’m attempting to create a report tell me what percentage of the grand total of records was completed in a given month. All I need is the percentage. The grand total is (for this example) is 2000.
I’m not sure if the actual gridview information/code is needed here but if it does, let me know and I’ll add it.
The problem is that I have been able to calculate the percentage total but when its displayed the percentage total is repeated for every single line in the table. I’m scratching my head on how to make this result appear only once.
Right now here’s what I have for my SQL code (I use nvarchar because we import from many non windows systems and get all sorts of extra characters and added spaces to our information):
Declare @DateCount nvarchar(max); Declare @DivNumber decimal(5,1); SET @DivNumber = (.01 * 2541); SET @DateCount = (SELECT (Count(date_record_entered) FROM dbo.tablename WHERE date_record_entered IS NOT NULL and date_record_entered >= 20131201 AND date_record_entered <= 20131231); SELECT CAST(ROUND(@DivNumber / @DateCount, 1) AS decimal(5,1) FROM dbo.tablename
Let’s say for this example the total number of records in the date_record_entered for the month of December is 500.
I’ve tried the smaller pieces of code separately with no success. This is the most recent thing I’ve tried.
I know I’m missing something simple here but I’m not sure what.
What I’m looking for as the expected result of my query is to have a percentage represented of records modified in a given month. If 500 records were done that would be 25%. I just want to have the 25 (and trainling decimal(s) when it applies) showing once and not 25 showing for every row in this table.
3 Solutions collect form web for “How to accurately figure percentage of a finite total”
The following query should provide what you are looking for:
Declare @DivNumber decimal(5,1); SET @DivNumber = (.01 * 2541); SELECT CAST(ROUND(@DivNumber / Count(date_record_entered), 1) AS decimal(5,1)) FROM dbo.tablename WHERE date_record_entered IS NOT NULL and date_record_entered >= 20131201 AND date_record_entered <= 20131231
Why do you select the constant value
cast(round(@divNumber / @DateCount, 1) as decimal(5,1)from the table? That’s the cause of your problem.
I’m not too familiar with sql server, but you might try to just select without a from clause.
select emp.Natinality_Id,mstn.Title as Nationality,count(emp.Employee_Id) as Employee_Count, count(emp.Employee_Id)* 100.0 /nullif(sum(count(*)) over(),0) as Nationality_Percentage FROM Employee as emp left join Mst_Natinality as mstn on mstn.Natinality_Id = emp.Natinality_Id where emp.Is_Deleted='false' group by emp.Natinality_Id,mstn.Title