Calculating Percentage in SQL

Sure...something like this:

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

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

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

Too slow :)

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?

Not decimal rather.

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

Select allOpen in new window

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?

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

Select allOpen in new window

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

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  

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.

Can you post your query?

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 ?

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

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

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

c'mon guys, you used my code / calculation and I did not even get an assist ?

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 ?

I just did.