Professional Web Applications Themes

Divide by Zero Query - Microsoft SQL / MS SQL Server

Hi..all, How to solve divide by zero error when I need to display all records in query? Example: Name Sales Target A 50 0 B 100 50 C 0 50 The query should resulting records like this : Name Sales Target Ach A 50 0 0 B 100 50 200 C 0 50 0 Thank you....

  1. #1

    Default Divide by Zero Query

    Hi..all,
    How to solve divide by zero error when I need to display
    all records in query?
    Example:
    Name Sales Target
    A 50 0
    B 100 50
    C 0 50

    The query should resulting records like this :
    Name Sales Target Ach
    A 50 0 0
    B 100 50 200
    C 0 50 0

    Thank you.
    Pitoyo Guest

  2. #2

    Default Re: Divide by Zero Query

    instead of just SELECT a/b
    use SELECT COALESCE(a/NULLIF(b,0),0)

    Tim S

    "Pitoyo" <com> wrote in message
    news:0b1901c35198$8d5bd330$gbl... 



    Tim Guest

  3. #3

    Default Re: Divide by Zero Query

    Pitoyo,

    If you want 0 as the values of [Ach] for row A and row C, then
    100*Sales/Target is not the right formula. If you want Sales/Target only
    when Target is not zero, and you want 0 otherwise, try

    select
    Name, Sales, Target,
    case when Target = 0 then 0 else 100*Sales/Target end as Ach
    ....

    -- Steve Kass
    -- Drew University
    -- Ref: D6E48B4F-C1CD-4566-A565-3AF6EEFB2525


    Pitoyo wrote:
     

    Steve Guest

Similar Threads

  1. Divide 24h into parts
    By marko in forum PHP Development
    Replies: 3
    Last Post: August 22nd, 03:12 AM
  2. Divide tool???
    By keiTai in forum Macromedia Freehand
    Replies: 1
    Last Post: July 22nd, 05:11 PM
  3. Divide by Zero in RDE
    By Dalibor Sramek in forum Ruby
    Replies: 0
    Last Post: October 9th, 08:26 PM
  4. divide
    By Phpu in forum PHP Development
    Replies: 4
    Last Post: September 10th, 02:44 PM
  5. Gap when using Divide
    By JuanMS webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 1
    Last Post: August 7th, 03:07 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139