Avatar of tddeffner
tddeffner asked on

Calculating Percentage in SQL

Hey All.

I am trying to write a sql statement that calculates a percentage from 2 columns and display the percentage in the third column.  IThe problem I seem to be having an issue with rounding, if they got 100% it displays but say if they got 24/25 it returns a 0.  I think it has something to do with rounding.

Any Suggestions?
Visual Basic ClassicMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
tddeffner

8/22/2022 - Mon
chapmandew

Sure...something like this:

select field1, field2, cast(field1 as decimal(18,3))/cast(field2 as decimal(18,3))
from
tablename
chapmandew

also, to get a %, you'll want to mult by 100

select field1, field2, (cast(field1 as decimal(18,3))/cast(field2 as decimal(18,3))) * 100
from
tablename
Patrick Matthews

Hello tddeffner,

If the data types of those columns are int, bigint, or tinyint, you need to convert them to a non-integer
data type:

SELECT ColA, ColB, CONVERT(numeric(9, 6), ColA) / CONVERT(numeric(9, 6), ColB) AS Perc
FROM SomeTable

Regards,

Patrick
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Patrick Matthews

Too slow :)
ASKER
tddeffner

Thanks Guys I am a step closer, now I have the right numbers, but now I need to round them down or up to the nearest whole number and not percentage.  What should I do?
ASKER
tddeffner

Not decimal rather.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Thomasian

hi tddeffner,

You can cast it again to decimal with 0 digits for decimal number.
select field1, field2, cast((cast(field1 as decimal(18,3))/cast(field2 as decimal(18,3))) * 100 as decimal(10,0))
from tablename

Open in new window

ASKER
tddeffner

Ah ok, Now my next question, am I going to be able to put this into a where statement?  Where they can search queries with a percentage criteria?  I know a simple where statement does not work.  How would I go about this piece?
Thomasian

You can just to put the formula in the where clause.

e.g.
select field1, field2, cast((cast(field1 as decimal(18,3))/cast(field2 as decimal(18,3))) * 100 as decimal(10,0))
from tablename
where cast((cast(field1 as decimal(18,3))/cast(field2 as decimal(18,3))) * 100 as decimal(10,0)) BETWEEN 90 AND 95

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Wills

Normally find all that cast is not needed if dealing with numerics... Normally enough to simply imply the dicision as decimal by multiply one of them by 1.0...


select field1, field2, round(((field1 * 1.0/field2) * 100.0),0)               -- nb if ),0,1) then will truncate
from tablename
where round(((field1 * 1.0/field2) * 100.0),0) BETWEEN 90 AND 95
Mark Wills

and in fact, you really should check for divide by zero...

select field1, field2, case when isnull(field2,0) <> 0 then cast(round(((field1 * 1.0/field2) * 100.0),0) as int) else 0 end as perc  
ASKER
tddeffner

I get this error when I try to put it in the where clause.

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Thomasian

Can you post your query?
Mark Wills

Basically, aggregates are the non group by columns, conversely if there is a column you are reporting on, and it is not part of the group by then it has to be covered by some kind of aggregate function (sum() max() etc). The having clause must contain one of those items in the select list, and if that item is an aggregate, then the having must be as well...

Can we see that query ?
ASKER
tddeffner

Hope this helps.  Here is the query:

SELECT A.Last_Name AS last_name, A.Last_4_SSN AS last_4_ssn, A.Birth_Date AS birth_date, q.Test_Description AS test_title, SUM(A.points) AS Points,
               SUM(A.poss_points) AS poss_points, ROUND(SUM(A.points) * 1.0 / SUM(A.poss_points) * 100.0, 0) AS Percentage
FROM  answer_tbl A INNER JOIN
               Quiz_tbl q ON q.Test_No = A.Test_No
WHERE A.user_div = 35 AND ROUND(SUM(A.points) * 1.0 / SUM(A.poss_points) * 100.0, 0) <= 75
GROUP BY A.Test_No, A.Category, A.Attempt, A.Last_Name, A.Last_4_SSN, A.Birth_Date, q.Test_Description
ORDER BY A.Last_Name
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Thomasian

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
tddeffner

That got it guys thanks.  Can I get a moderator to remove the query?
Thomasian

I'm not sure... Click "Report Abuse" and try to ask a moderator if that is possible.
Mark Wills

c'mon guys, you used my code / calculation and I did not even get an assist ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mark Wills

you could ask the moderator at the same time you are wanting to hide your code. they can perform marvels... trouble is that the "answer" also uses your code, so might not be able to hide / remove it. have you asked the question yet ?
ASKER
tddeffner

I just did.