# Returning a "0" - When using a divide be (/)

• ### Question

• im very confused in the following query...

I have two select statements..

Which produce the following results.

1st SELECT STATEMENT - 5916
2nd SELECT STATEMENT - 22630

Then if i divide the 1st statement by the 2nd statement i should get 0.246.... this is currently done via a calculator...

Which will be used as a percentage in my Reporting Services report.

But when i use TSQL to divide the 1st SELECT against the 2nd SELECT it returns a 0?

What am i Doing wrong

DECLARE

@StartDate AS DATETIME

DECLARE

@EndDate AS DATETIME

SET

@StartDate = DATEADD(ww,-6,GETDATE()) --TODAYS DATE, MINUS 6 WEEKS

SET

@EndDate = GETDATE() --TODAYS DATE

SELECT

(

SELECT

sum(case when status = 'Apple' then 1 else 0 end)

FROM

MyTable

WHERE

Status

= 'y'

and Date > @startdate and Date < dateadd(dd, 1, @enddate)

)/(

SELECT

sum(case when status IN ('Apple','Orange') then 1 else 0 end)

FROM

MyTable

WHERE

Status

= 'y'

and Date > @startdate and Date < dateadd(dd, 1, @enddate)

)

Wednesday, October 13, 2010 1:23 PM

• The reason for this result is so called 'integer math' division. When you divide one integer by another integer, the result is going to be an integer as well.

The solution is to cast one of the dividends to the desired type, e.g.

cast(OneNumber as decimal(10,2))/OneInteger

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
• Marked as answer by Wednesday, October 13, 2010 1:39 PM
Wednesday, October 13, 2010 1:28 PM

### All replies

• SELECT

5916.0/22630.0

cast those values as DECIMAL/NUMERIC... or just add .0

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Wednesday, October 13, 2010 1:26 PM
• The reason for this result is so called 'integer math' division. When you divide one integer by another integer, the result is going to be an integer as well.

The solution is to cast one of the dividends to the desired type, e.g.

cast(OneNumber as decimal(10,2))/OneInteger

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
• Marked as answer by Wednesday, October 13, 2010 1:39 PM
Wednesday, October 13, 2010 1:28 PM
• The other replies are accurate regarding the output data type.  My message is in regards to the query you posted.  It can be more efficient if written a different way.  The way it is written, there are 2 queries executed that have the same table and the same where clause.  Depending on the size of your table, there may not be any noticeable difference in execution time, but with a larger table, it probably will be noticeable.

```DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME

SET @StartDate = DATEADD(ww,-6,GETDATE()) --TODAYS DATE, MINUS 6 WEEKS

SET @EndDate = GETDATE() --TODAYS DATE

SELECT 1.0 * sum(case when status = 'Apple' then 1 else 0 end)/sum(case when status IN ('Apple','Orange') then 1 else 0 end)
FROM  MyTable
WHERE Status = 'y'
and Date > @startdate
and Date < dateadd(dd, 1, @enddate)

```

It's also possible that this is not the most efficient query either, depending on your data and what indexes you have on the table.  The reason I posted at all is because modifying the query that way that I have shown is a valuable technique that can be used to improve performance on some queries.

Wednesday, October 13, 2010 1:38 PM