2

I did this following query, however it gave the the result of 0 for each orderStatusName, does anyone know where is the problem? 

with tbl as (
      select s.orderstatusName, c.orderStatusId,count(c.orderId) counts 
      from [dbo].[ci_orders] c left join
           [dbo].[ci_orderStatus] s 
           on s.orderStatusId = c.orderStatusId
      where orderedDate between '2018-10-01' and '2018-10-29'
      group by orderStatusName, c.orderStatusId
    )
select orderstatusName, counts/(select sum(counts) from tbl as PofTotal) from tbl

the result is :0

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Tea
  • 23
  • 2

2 Answers2

7

You're using what is known as integer math. When using 2 integers in SQL (Server) the return value is an integer as well. For example, 2 + 2 = 4, 5 * 5 = 25. The same applies to division 8 / 10 = 0. That's because 0.8 isn't an integer, but the return value will be one (so the decimal points are lost).

The common way to change this behaviour is to multiply one of the expressions by 1.0. For example:

counts/(select sum(counts) * 1.0 from tbl) as PofTotal

If you need more precision, you can increase the precision of the decimal value of 1.0 (i.e. to 1.000, 1.0000000, etc).

Thom A
  • 88,727
  • 11
  • 45
  • 75
1

Use window functions and proper division:

select orderstatusName, counts * 1.0 / total_counts
from (select t.*, sum(counts) over () as total_counts
      from tbl
     ) t;

The reason you are getting 0 is because SQL Server does integer division when the operands are integers. So, 1/2 = 0, not 0.5.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786