Search code examples
t-sqlcountdivide

Divide returning 0 with two count functions?

Can anyone please help as to why the below division is retuning 0 for all lines?

> COUNT (distinct dd.item) / COUNT (distinct pb.item) *100 as 'In Stock %'

I am using both COUNTS in the select statement further up and they are returning the correct results.

Full Query is

    SELECT
pb.hierarchy1,
H1.Hierarchy_Description,
COUNT (distinct dd.item) as 'Lines',
COUNT (distinct dd.item) / @TotalLines *100 as [Lines %],
SUM (dd.qty_ordered) as QTY_Ordered,
SUM (dd.qty_ordered) / @TotalItems *100 as [Ordered %],
COUNT (distinct pb.item) as 'Lines In Stock',
COUNT (distinct dd.item) / COUNT (distinct pb.item) *100 as 'In Stock %'
from distbo004.dbo.product_branch as pb
left join distbo004.dbo.product_stockpurchases as ps on pb.branch=ps.branch and pb.item=ps.item
left join Distbo004.dbo.hierarchy as H1 on h1.Hierarchy1 = pb.hierarchy1 and h1.Hierarchy2 = '' and h1.Hierarchy3 = '' and h1.Hierarchy4 = '' and h1.Hierarchy5 = '' and pb.hierarchy1 <> ''
left join Distbo004.dbo.delivered_detail as dd on pb.branch=dd.branch and pb.item=dd.item and (dd.orderinvoiced between @sdate and @edate and dd.qty_ordered<>dd.qty_picked and dd.original_qty<>0)
left join (select branch, cust_code,del_order_no,item,description,pack_description,substitute_line_no,qty_picked,pack from Distbo004.dbo.delivered_Detail where orderinvoiced between @sdate and @edate)
            as dd1 on dd.branch=dd1.branch and dd.del_order_no=dd1.del_order_no and dd1.substitute_line_no=dd.line_no --and (dd.item<>dd1.item or dd1.item is null)
left join Distbo004.dbo.delivered_header dh on dh.branch=dd.branch and dh.del_order_no=dd.del_order_no and (dh.status='completed' and dh.rep not in (86,87,89,88,90))

WHERE pb.branch=@branch 
and pb.hierarchy1 NOT IN (' ',00,17,18,19,20,21,22,23,24,25,29)
and ps.qty_in_stock<>0
and (dd.item<>dd1.item or dd1.item is null)

group by
pb.branch,
pb.hierarchy1,
H1.Hierarchy_Description

thanks in advance

Solution

  • Problem you are having is that COUNT returns result as integer data type (documentation).

    So you get two integers like 3 / 339 which returns 0 because of data type used.

    You should use CAST to solve this like:

    SELECT CAST(COUNT(distinct dd.item)as float)/CAST(COUNT(distinct pb.item)as float) *100 as 'In Stock %'