Jeff Jeff - 1 month ago 8
SQL Question

Getting the percent of a column related to the entire sum of a column sql

I am trying to get the percent of the yearly forecast column related to the overall total forecast for a specific product NDC. I have the code below but it is returning the value 0 every time. Any ideas?

select
f.ProductNDC ProductNDC,
g.IMDSC1 ItemDesc,
g.IMDSC2 ItemDesc2,
cf.DirectUsage YearlyForecast,
(cf.DirectUsage / (SUM(cf.DirectUsage) over(partition by f.ProductNDC))),
cd.CustomerNum CustomerNumber,
cd.CustomerName CustomerName,
cf.EffectiveDate EffectiveDate,
cf.EndDate EndDate
From Forecast_Dev.dbo.Forecast f
join Forecast_Dev.dbo.CustomerForecast cf
on cf.ForecastID = f.ForecastID
join Forecast_Dev.dbo.CustomerDetail cd
on cd.CustomerID = cf.CustomerID
join JDE_DEVELOPMENT.TESTDTA.F4101 g
on ltrim(rtrim(g.imlitm))= f.ProductNDC

Answer

Some databases do integer division, meaning that the ratio of two integers is an integer. So, 1 / 2 = 0, rather than 0.5.

So, turn this to a floating point number of some sort:

(cf.DirectUsage * 1.0 / nullif(SUM(cf.DirectUsage) over(partition by f.ProductNDC))), 0),