[RESOLVED] Simple Math Problem in SQL Query

Rampage-Obidos

I seem to only ever visit this forum when I have come unstuck, and this time is no exception. 🙂

I have been racking my brain all day trying to work out the flaw with my math in this part of my SQL query:

ROUND(100 - (StockPoole.[TotalStock]/(StockPoole.[TotalStock]+StockSoton.[TotalStock]) * 100),2) AS [Percentage]

This is where I become unstuck:

ROUND(100 - (526/(526+-31)* 100),2) COLOR="Magenta"[/COLOR] 0

ROUND(100 - (23/(23+0)* 100),2) COLOR="Magenta"[/COLOR] 0

ROUND(100 - (8/(8+1)* 100),2) COLOR="Magenta"[/COLOR] 100

The Magenta colour is the PHP answer and the Lime colour is the SQL answer.

I am not sure why/how SQL and PHP can create different answers, if I was I wouldn't be here 🙂

Any and all insight would be most appreciated, thanks.

Lars_Berg

The DBMS that you are using is probably using integer division.

Cast the values to decimal before doing the arithmetic.

Rampage-Obidos

Brilliant, thank you very much.

Just for reference, the second I added the cast it worked, however then the rounding didn't, any reason? Either way casting to decimal solved my issue, thanks 🙂

100 - (CAST(StockPoole.[TotalStock] AS DECIMAL)/(StockPoole.[TotalStock]+StockSoton.[TotalStock]) * 100)