Calculating a percentage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating a percentage

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-03-25 : 15:28:36

My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:

---------------------
Members
---------------------
ID | Goal
---------------------

---------------------
Donors
---------------------
Amount | MemberID
---------------------

I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:

CREATE PROCEDURE sproc_GetGoalPercentage
(
@memberid int
)
AS
SELECT (SUM(D.Amount)/M.Goal)*100
FROM Members AS M INNER JOIN Donors AS D
ON M.ID = D.MemberID
WHERE M.ID = @memberid
GROUP BY M.Goal
RETURN

All it does is return 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 15:44:34

Try this:-

CREATE PROCEDURE sproc_GetGoalPercentage 
(
@memberid int
)
AS
SELECT (D.Total * (1.0)/M.Goal)*100
FROM Members AS M 
INNER JOIN (SELECT MemberID,SUM(Amount) AS Total 
FROM Donors 
GROUP BY MemberID) D
ON M.ID = D.MemberID
WHERE M.ID = @memberid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 16:00:55

There is no need for extra set of parathesises

SELECT 100.0 * D.Total / M.Goal

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 16:06:48

quote:
Originally posted by Peso

There is no need for extra set of parathesises

SELECT 100.0 * D.Total / M.Goal

E 12°55'05.25"
N 56°04'39.16"

Any problems if we put an extra parenthesis?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 16:08:14

Ease of readness? An extra multiplication?

E 12°55'05.25"
N 56°04'39.16"

Apples
Posting Yak Master

146 Posts

Posted - 2008-03-25 : 16:33:28

That works great, but I have another question.

If the member hasn't been donated anything, it doesn't work. How can I change it so that if the member hasn't received donations, it returns 0 as the percentage?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 16:42:46

Use LEFT JOIN Donors, and COALESCE Amount with 0.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 16:45:56

[code]

SELECT m.ID,
COALESCE(100.0 * y.amt / m.Goal, 0.0) AS Percentage
FROM Members AS m
LEFT JOIN (
SELECT MemberID,
SUM(Amount) AS amt
FROM Donors
GROUP BY MemberID
) AS y ON y.MemberID = m.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources