How do I calculate percentages with decimals in SQL?

Question

How can i convert this to a decimal in SQL? Below is the equation and i get the answer as 78 (integer) but the real answer is 78.6 (with a decimal) so i need to show this as otherwise the report won't tally up to 100%

(100 * [TotalVisit1]/[TotalVisits]) AS Visit1percent

score 25 · Answer 1 · answered Dec 30 '08 at 15:34

25

Try This:

(100.0 * [TotalVisit1]/[TotalVisits]) AS Visit1percent

answered Dec 30 '08 at 15:34

LeppyR64

5,251
2
30
35

1

Make sure you add formatting in your presentation layer to round that down to 1 or 2 decimal places. Otherwise, you're likely to run into things like 33.33333... – Michael Haren Dec 30 '08 at 15:38

score 10 · Accepted Answer · edited Dec 13 '13 at 13:40

10

convert(decimal(5,2),(100 * convert(float,[TotalVisit1])/convert(float,[TotalVisits]))) AS Visit1percent

Ugly, but it works.

edited Dec 13 '13 at 13:40

Prahalad Gaggar

11,389
16
53
71

answered Dec 30 '08 at 15:41

Shawn

19,465
20
98
152

Thanks this is the one ive gone with as it rounded up the number, the other answers all worked as well though thanks allot every one :) – Dec 30 '08 at 15:44
2

I've found it better to convert to decimal than float. Certain divisions work better with decimal, at least on Microsoft SQL server: SELECT 5555/CAST(1000 AS float), CAST(5555/CAST(1000 AS float) AS numeric(5, 2)), 5555/CAST(1000 AS decimal), CAST(5555/CAST(1000 AS decimal) AS Numeric(5, 2)) – user12861 Dec 30 '08 at 18:50

score 2 · Answer 3 · edited Jun 13 '17 at 17:47

2

CAST(ROUND([TotalVisit1]*100.0/[TotalVisits],2)as decimal(5,2)) as Percentage

Not ugly and work better and fast , enjoy it!

edited Jun 13 '17 at 17:47

ɐlǝx

1,384
2
17
22

answered Jun 13 '17 at 15:03

Ali

21
1

score 1 · Answer 4 · answered Dec 30 '08 at 15:34

1

At least in MySQL (if it helps), if you want to use float numbers you had to use a type float field, not the regular int fields.

answered Dec 30 '08 at 15:34

Ólafur Waage

68,817
22
142
198

score 1 · Answer 5 · answered Dec 30 '08 at 15:49

1

Just add a decimal to the 100

(100.0 * [TotalVisit1]/[TotalVisits]) AS Visit1percent

this forces all processing to happen in floats... if you want the final output as text, and truncated for display to only one decimal place, use Str function

Str( 100.0 * [TotalVisit1]/[TotalVisits], 4, 1 ) AS Visit1percent

answered Dec 30 '08 at 15:49

Charles Bretana

143,358
22
150
216

Like your str answer nice and neat looking :) – Dec 30 '08 at 16:21

score 1 · Answer 6 · answered Jan 10 '20 at 19:57

1

This works perfectly for me:

CAST((1.0 * SUM(ColumnA) /COUNT(ColumnB)) as decimal(5,2))

Hope it helps someone out there in the world.

answered Jan 10 '20 at 19:57

Goku

197
1
7

score 0 · Answer 7 · answered May 29 '13 at 21:36

SELECT(ROUND(CAST(TotalVisit1 AS DECIMAL)/TotalVisits,1)) AS 'Visit1percent'

This will return a decimal and the ROUND will round it to one digit. So in your case you would get 76.6. If you don't want any digits change the 1 to 0 and if you want two digits change it to 2.

score 0 · Answer 8 · answered Dec 30 '08 at 15:40

Its probably overkill to do this, but you may wish to consider casting all values to floats to ensure accuracy at all phases.

(100.0 * ( [TotalVisit1]+0.0 )/([TotalVisits]+0.0) ) as Visit1percent

Note, you really need to put code in here for the case that TotalVisits == 0 or you will get a division by 0 answer.

score 0 · Answer 9 · answered Aug 02 '17 at 12:39

Try with this, no round and str or Over(). i found this as a simpler way to do it.

cast((count(TotalVisit1) * 100.0) /TotalVisits as numeric(5,3))  as [Visit1percent]

You can change the number of decimal points as you wish to

e.g. numeric(5,2) or numeric(5,4)

score -1 · Answer 10 · answered Aug 22 '13 at 21:26

-1

In ms Access You can use the SQL function ROUND(number, number of decimals), It will round the number to the given number of decimals:

ROUND((100 * [TotalVisit1]/[TotalVisits]),1) AS Visit1percent

answered Aug 22 '13 at 21:26

larssbr

25
6

score -1 · Answer 11 · answered Dec 30 '08 at 16:39

This might not address you issue directly, but when you round a set of numbers for display you're never guaranteed to get numbers that add to 100 unless you take special precautions. For example, rounding 33.33333, 33.33333 and 33.33333 is going to leave you one short on the sum, so the logical thing to do is to modify the percentage for the largest value in the set to take account of any difference.

Here's a way of doing that in Oracle SQL using analytic functions and a subquery factoring (WITH) clause to generate sample data.

with data as (select 25 num from dual union all
              select 25     from dual union all
              select 25     from dual)
select num, 
case
   when rnk = 1
   then 100 - sum(pct) over (order by rnk desc
                             rows between unbounded preceding
                                      and         1 preceding)
   else pct
end pct
from
   (
   select num,
          round(100*ratio_to_report(num) over ()) pct,
          row_number() over (order by num desc) rnk
   from data
   )
/


NUM                    PCT                    
---------------------- ---------------------- 
25                     33                    
25                     33                     
25                     34

This is a bad idea in general. If the numbers don't add to 100 due to rounding, then that's how it is. Monkeying around with them is just going to give less accurate results. — user12861, Dec 30 '08 at 18:46
It's neither a good nor a bad idea, and generalities don't come into it. It's just a technique for achieving a specific hypothetical requirement. — David Aldridge, Dec 31 '08 at 01:26
True, but it's a bad requirement. It leads to incorrectly displayed data. And it doesn't answer the question that was posed above. And it's not the most accurate solution to making the percents add to 100 either, though it may be the simplest. — user12861, Dec 31 '08 at 14:17

How do I calculate percentages with decimals in SQL?

11 Answers11

Linked