Column returning 0 when trying to find percentage [SSMS]

M.Ustun 0
Viewed 26

I have the following query and trying to find the percentage however the column consistently returns 0. Does anyone know the logic as to why and how I can rectify this. The column in question is Percentage.

The values received in my query is below:

Number of sales to target v2t2 v1T Percentage
311 175 486 0 
select 
(v1.Total - v2.TotalUnits) as [Number Of Sales To Target], 
(v2.TotalUnits)  as v2t2,
v1.Total as v1T,
(v2.TotalUnits / v1.Total) as [Percentage]
from  --Change minus to a divide if you want %

table1 v1 Left Join table2 v2 ON v1.ID = v2.ID

I've tried casting as float and decimal but still didn't work.

ssms sql-server sql
2 Answers

Based on this SO you can try something like

select 
(v1.Total - v2.TotalUnits) as [Number Of Sales To Target], 
(v2.TotalUnits)  as v2t2,
v1.Total as v1T,
((v2.TotalUnits*1.0) / v1.Total)*100 as [Percentage]
from  --Change minus to a divide if you want %

table1 v1 Left Join table2 v2 ON v1.ID = v2.ID

Notice v2.TotalUnits*1.0 also you will need to multiply by 100 to get the percentage

jitender0

If you divide an integer with another integer the result type will also be an integer. Example:

declare @a int = 7
declare @b int = 8

select @a/@b --Will return 0
select cast(@a as decimal)/cast(@b as decimal) --Will return 0.875
userH1Y10
Related