CODEWITHSUNDEEP
sql
Jeff
Jeff

Reputation: 449

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

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

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),

Upvotes: 1

Related Questions