Getting "0" in simple mathematical function through window function

Question

I need to find the percent the product was on shelf.

So I use this query:

select 
    *, 
    cast((t1.CountProd / t1.SumProd)*100 as dec(12,10)) as asasa
from 
   (select 
        *,
        count(Nalichie) over (partition by ProductID) as CountProd,
        count(Nalichie) over() as SumProd
    from 
        [saleit_db_Danone].[OSA].[tbl_FullFCompetitive]) as t1

Why do I get 0 in the last column?

Here is my result:

Cast the columns `t1.CountProd` and `t1.SumProd` explicitly. At the moment you are doing an integer division `1936 / 202223 = 0`. — Chris Pickford, Jun 15 '16 at 13:53
Make them *not* integers. Cast both columns separately before doing the division, not after. — RBarryYoung, Jun 15 '16 at 14:10

Chris Pickford · Answer 1 · 2016-06-15T14:36:21.130

3

As per comment, you are doing an integer division then casting to a decimal afterwards. Try this:

select
    *,
    (CAST(CountProd AS DEC(16, 10)) / SumProd) * 100 AS asasa
from (
    select
        *,
        count(Nalichie) over (partition by ProductID) as CountProd,
        count(Nalichie) over() as SumProd
    FROM [saleit_db_Danone].[OSA].[tbl_FullFCompetitive]
) as t1

edited Jun 15 '16 at 14:36

answered Jun 15 '16 at 13:56

Chris Pickford

8,642
5
42
73

1

Up vote, but I have an additional note. Based on the numbers in the screenshot this leads to an Arithmetic overflow error (when I ran it anyway), so I think @Andrey will need to increase the size of the datatype. I went up to DEC(16,10) and it worked with the example data. – TLaV Jun 15 '16 at 14:14
thats won't working. Becose first sql find count ant it is alredy int and then you try to convert into decimal.Ypu will receive an converting error. Solve this trick by adding an `t1.CountProd + 0.0` without converting – Andriy Jun 15 '16 at 14:14
If `CountProd ` and `SumProd ` are integer - that will cause arithmetic overflow error on converting – gofr1 Jun 15 '16 at 14:14
Updated to `DEC(16,10)`. Tested and working. See [working example](https://gist.github.com/chrispickford/905343507347cfa309a7fd708c722837). – Chris Pickford Jun 15 '16 at 14:30

score 0 · Answer 2 · answered Jun 15 '16 at 14:22

0

Also if you convert into numeric instead decimal you will get a correct answer.

answered Jun 15 '16 at 14:22

Andriy

123
1
9

Getting "0" in simple mathematical function through window function

2 Answers2