How Do I Calculate Percentage In Sql Of Three Differnt Values

Question

0.00/5 (No votes)

See more:

Hi ,
Say in my table i have three columns draft , live and completed

values are 18 Draft , 33 live ,11 completed

How can i calculate percentage of each in sql

I tried somthing like this .. but dont think this is calculating corrwectkly

SQL

UPDATE @finalTable
    SET Draft_Percentage =  (CASE
    when Draft_Total > 0 then ((Draft_Total * 100) / (select sum(t1.Draft_Total) + sum(t1.Live_Total) + sum(t1.Completed_Total)  from @finalTable as t1))
    else 0
    end)

    UPDATE @finalTable
    SET Live_Percentage = (case
    WHEN Live_Total > 0 THEN ((Live_Total * 100) / (select sum(t1.Draft_Total) + sum(t1.Live_Total) + sum(t1.Completed_Total) from @finalTable as t1))
    else 0
    end)

    UPDATE @finalTable
    SET Completed_Percentage = (CASE
    WHEN Completed_Total > 0 THEN ((Completed_Total * 100) / (select sum(t1.Draft_Total) + sum(t1.Live_Total) + sum(t1.Completed_Total) from @finalTable as t1))
    else 0
    end)

Posted 13-May-15 19:52pm

Torakami

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mycroft Holmes · Answer 1 · 2015-05-13T20:14:00

Solution 1

Do it as 2 queries.

SQL

Select KeyField, Sum(Live) as Live, Sum(Draft) as Draft, Sum(Completed) as Completed
From TableName
Where  x=@X
group By KeyField

Then nest that in your outer query that does the calcs

SQL

Select C.Live /(C.Draft + C.Completed)
From (Select KeyField, Sum(Live) as Live, Sum(Draft) as Draft, Sum(Completed) as Completed
From TableName
Where  x=@X
group By KeyField) C

Posted 13-May-15 20:14pm

Mycroft Holmes

Comments

Torakami 14-May-15 2:28am

Thanks for your soultion but i didnt get this one , Can you please show me with my example , i didnt get this . I am actauly updating the value .

Mycroft Holmes 14-May-15 2:36am

Do you understand the first query, you want a result with 3 fields (4 if you need to do a join back onto the source table) which are the sum of the values you need to calculate.

Santhosh Babu Mahimairaj · Answer 2 · 2015-05-13T21:29:00

SQL

DECLARE @CalculateAvg AS TABLE (Name NVARCHAR(50),
                                Draft_Total FLOAT, 
                                Live_Total FLOAT,
                                Completed_Total FLOAT)
                                
                                
  INSERT INTO @CalculateAvg(Name,
							Draft_Total,
							Live_Total,
							Completed_Total)							
						VALUES ('Santhosh',100,200,300),
						       ('Dani',100,200,300),
						       ('Leo',100,200,300),
						       ('Miltone',100,200,300),
						       ('Santhosh',200,400,600),
						       ('Dani',600,700,700),
						       ('Leo',700,800,300),
						       ('Miltone',700,800,800),
						       ('Santhosh',100,200,300),
						       ('Dani',600,500,400),
						       ('Leo',400,600,600),
						       ('Miltone',500,700,800)
						       
						       
						       
						     
DECLARE @Draft_Total INT =  (SELECT SUM(Draft_Total) FROM @CalculateAvg )

DECLARE @Live_Total INT =  (SELECT SUM(Live_Total) FROM @CalculateAvg )		

DECLARE @Completed_Total INT =  (SELECT SUM(Completed_Total) FROM @CalculateAvg )				       
						       
 SELECT ((SUM(Draft_Total) * 100 ) / @Draft_Total) Draft_Total,
		((SUM(Live_Total)* 100) / @Live_Total)Live_Total ,
		((SUM(Completed_Total) * 100 ) / @Completed_Total) Completed_Total 
		 FROM @CalculateAvg

Torakami · Answer 3 · 2015-05-14T03:47:00

UPDATE n1
	SET n1.Draft_Percentage =  (CASE 
	when n1.Draft_Total > 0 then ( Round((cast((n1.Draft_Total * 100)as decimal) / (select (t1.Draft_Total+ t1.Live_Total+ t1.Completed_Total) 
										from @finalTable as t1 where t1.DepartmentName=n1.DepartmentName)),2)) 
	else 0 
	end)
	from @finalTable as n1

	UPDATE n1
	SET n1.Live_Percentage =  (CASE 
	when n1.Live_Total > 0 then (Round(( cast((n1.Live_Total * 100) as decimal) / (select (t1.Draft_Total+ t1.Live_Total+ t1.Completed_Total) 
									from @finalTable as t1 where t1.DepartmentName=n1.DepartmentName)),2))
	else 0 
	end)
	from @finalTable as n1

	UPDATE n1
	SET n1.Completed_Percentage =  (CASE 
	when n1.Completed_Total > 0 then (Round((cast((n1.Completed_Total * 100) as decimal) / (select (t1.Draft_Total+ t1.Live_Total+ t1.Completed_Total) 
									from @finalTable as t1 where t1.DepartmentName=n1.DepartmentName)),2)) 
	else 0 
	end)
	from @finalTable as n1

How Do I Calculate Percentage In Sql Of Three Differnt Values

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0