Results 1 to 2 of 2

Thread: Newbie mystery

  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: Newbie mystery

    Try running the code below in QA

    USE Northwind
    --The quesy below produces the correct numbers.
    SELECT CategoryID,(100*((COUNT(*)+.0)/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

    --The query below produces 0 values and are wrong.
    SELECT CategoryID,(100*((COUNT(*))/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

    --This is the total
    SELECT COUNT(*) AS TotalCount FROM Products
    --The totals of the groupings
    SELECT CategoryID,(COUNT(*)) AS Category_Total FROM Products GROUP BY CategoryID

    I think I understand what happens with the above, but what I really want to know is there a good coding habit to prevent it. Some of our reports are very complex and an error could be missed.

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Dear,

    In MSSQL, this is normal behaviour. When you divide an integer (count) by another integer (count), the result is an integer.

    Example :

    select 1/3 returns 0

    select 1/cast(3 as numeric) returns .33333

    That is just the way it is, and it is documented in BOL.

    Regards,

    CVM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •