How do I calculate percentages with decimals in SQ

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

Answer 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.

Answer 2

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.

Answer 3

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

Ugly, but it works.

Answer 4

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

Answer 5

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.

Answer 6

Try This:

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