SQLTeam.com | Weblogs | Forums

Dividing counts in SQL server returns a quotient of 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?

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

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.

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

1 Like

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

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

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