SQLTeam.com | Weblogs | Forums

Getting Percentage of Two Sums


#1

Hi

I am trying to calculate an overall percentage complete from a table and return the Overall Percentage back as a single column in an Select (as I want to use the percentage in another select; this should be a sub-query)

I have the calculations working to get TotalEffort and CompletedEffort - see below - but can someone tell me how to then calculate the percentage of these two columns?

For example, the select below would return 510 for both columns so the Overall Percentage Competed would be 100

Thanks

Select
Sum(P.Duration) as TotalEffort,
Sum(((Cast(P.Duration as Decimal) / 100) * Cast(P.PercentComplete as Decimal))) as CompletedEffort
FROM
[Portfolio Register] PR INNER JOIN Workstreams W ON PR.[Project Reference] = W.[Project Reference]
INNER JOIN Plans P ON W.ID = P.WorkstreamID
Where
PR.[Project Reference] = 'PR00277 Study' and Duration > 0


#2

Ignore me (I think!).

How come I missed the obvious, which I think is...

(Sum(((Cast(P.Duration as Decimal) / 100) * Cast(P.PercentComplete as Decimal))) / Sum(P.Duration)) * 100 as CompletedEffort

If I am wrong, please let me know

Thanks

Phil


#3

Take the ratio of the two (and multiply by 100 if you want to get it as percent)

SELECT
    SUM(P.Duration) AS TotalEffort ,
    SUM(( ( CAST(P.Duration AS DECIMAL) / 100 )
          * CAST(P.PercentComplete AS DECIMAL) )) AS CompletedEffort,
          
    100.0E * SUM(P.Duration)  /
     SUM(( ( CAST(P.Duration AS DECIMAL) / 100 )
          * CAST(P.PercentComplete AS DECIMAL) )) AS PercentCompleted
   
FROM
    [Portfolio Register] PR
    INNER JOIN Workstreams W
        ON PR.[Project Reference] = W.[Project Reference]
    INNER JOIN Plans P
        ON W.ID = P.WorkstreamID
WHERE
    PR.[Project Reference] = 'PR00277 Study'
    AND Duration > 0

Although the performance and logic are the same, the following is easier on the eye. so I would use the following.

SELECT
	TotalEffort,
	CompletedEffort,
	100.E * TotalEffort / NULLIF( CompletedEffort, 0 ) AS PercentCompleted
FROM
(
	SELECT
		SUM(P.Duration) AS TotalEffort ,
		SUM(( ( CAST(P.Duration AS DECIMAL) / 100 )
			  * CAST(P.PercentComplete AS DECIMAL) )) AS CompletedEffort
	FROM
		[Portfolio Register] PR
		INNER JOIN Workstreams W
			ON PR.[Project Reference] = W.[Project Reference]
		INNER JOIN Plans P
			ON W.ID = P.WorkstreamID
	WHERE
		PR.[Project Reference] = 'PR00277 Study'
		AND Duration > 0
) s;

#4

Thanks James - much better

But shouldn't the calculation line be...

(NULLIF( CompletedEffort, 0 ) / TotalEffort) * 100.E AS PercentCompleted ?

Otherwise I get percentages greater than 100


#5

My bad!! :smile:

The NULLIF is really to avoid division by 0. So I would change it to this:

100.E * CompletedEffort / NULLIF( TotalEffort, 0 ) AS PercentCompleted

Whenever there is a division, I always try to take into account the possibility that the denominator might be zero.


#6

:slight_smile: Good point on the Null If, will remember that

Phil