none
Returning a "0" - When using a divide be (/) RRS feed

  • 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

Answers

  • 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 Sam233 Wednesday, October 13, 2010 1:39 PM
    Wednesday, October 13, 2010 1:28 PM
    Moderator

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
    Answerer
  • 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 Sam233 Wednesday, October 13, 2010 1:39 PM
    Wednesday, October 13, 2010 1:28 PM
    Moderator
  • 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