Dividing counts in SQL server returns a quotient of 1

Other Tech Topics
1

I am doing an analysis of shooting incidents in NYC over the last fifteen years and am endeavoring to find the percentage of shootings that resulted in deaths.

I divide count of murders by count of total shootings using the following syntax:

select format(count(MURDERED)/count(*), 'P1') MURDER_PCT from PortfolioProjects..NYPD_shootings where MURDERED = 'TRUE'

This returns an answer of 1 (or 100% with percent formatting). What is wrong with my syntax?

2

Nothing is wrong with the syntax or the result as an integer divided by an integer is an integer. If you want decimal places you need to cast to a datatype which supports them. The easiest way to do that is to multiply by 1.0.

eg COUNT(murdered) * 1.0 / COUNT(*)

1 Like
3

Thanks for responding, but that is not my issue. The answer should not be 1; not all of the shootings resulted in murders. Specifically, the total number of shootings is 23,565 and the number that resulted in murders is 4,448. So the answer I get should be somewhere close to 0.2, or 20%. Please advise.

4

An integer cannot be 0.2. You need to cast your numbers to a type which allow decimal places.

1 Like
5

COUNT returns type int. You are dividing one COUNT by another.

6

The WHERE condition:

where MURDERED = 'TRUE'

will exclude all non-murders. Remove that condition if you want to consider non-murders in the total.

1 Like
7

you probably can't do count(murdered) anyway once you remove the where condition since it'll count both true and false values. Need to do a case when or cast

select (sum(cast(murdered as int)) * 1.0) /count(*), 'P1') MURDER_PCT from PortfolioProjects..NYPD_shootings

1 Like