1

I have the following code

SELECT 
    Country,  
    COUNT(*) AS Records, 
    COUNT(SpecialField) AS Cnt, 
    COUNT(SpecialField)/COUNT(*) AS Perc_Hit 
FROM 
    Table
GROUP BY 
    Country
ORDER BY 
    Cnt DESC, Records DESC

This query returns 0s for the Perc_Hit column even though it shouldn't. For example, one China has 1000 'records' and 853 'counts', so I'm expecting .853 as my result.

Can someone tell me why there's this error?

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Farellia
  • 187
  • 1
  • 2
  • 14
  • 6
    An INT/INT will return an INT try COUNT(SpecialField)/(COUNT(*) +0.0) – John Cappelletti Apr 04 '17 at 20:11
  • 1
    @JohnCappelletti When you hover your mouse over `add a comment` then title is displayed and it reads "Use comments to ask for more information or suggest improvements. Avoid answering questions in comments." -- Please post your answer. – SqlZim Apr 04 '17 at 20:18
  • @Anand Not if SpecialField contains nulls. Nulls would be excluded from the count – John Cappelletti Apr 04 '17 at 20:25
  • Check for null rows in the column you are counting. – Luis Teijon Apr 04 '17 at 20:26
  • @Anand `count()` doesn't count nulls, so if just 1 row has a null, the result will be zero – Bohemian Apr 04 '17 at 20:26
  • Possible duplicate of [How to get a float result by dividing two integer values?](http://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values) – Lasse V. Karlsen Apr 04 '17 at 20:34

2 Answers2

1

As pointed out in @JohnCappelletti's comment, you are performing integer division. You can simply add .0 to either side like so:

select 
    Country
  , count(*) as Records
  , count(SpecialField) as Cnt
  , count(SpecialField)/(count(*)+.0) as Perc_Hit 
from Table
group by Country
order by Cnt desc, Records desc
SqlZim
  • 37,248
  • 6
  • 41
  • 59
0

You should change:

COUNT(SpecialField)/COUNT(*) AS Perc_Hit

to this:

CAST(COUNT(SpecialField) as float)/CAST(COUNT(*) as float) AS Perc_Hit
BWS
  • 3,786
  • 18
  • 25