Professional Web Applications Themes

how to keep sum() from rounding - Microsoft SQL / MS SQL Server

The first statement rounds to two decimal places, if I remove the sum ( after the union) (second statement) NO rounding occurs. Anyone know of a way to keep it from rounding and still sum the number? ///////////////////////////////////////////// SELECT sum((calls.rated_toll_cost + calls.tax_5_amount) - calls.call_volume_discount_amount * call_cost_factor) FROM (calls join cost_groups on cost_group_nbr = call_cost_group and report_group = 'all') join organization on organization.organization = calls.organization WHERE switch_ext = '508' and site = 'BOS' and call_cost_group = '16' and calls.organization is not null UNION ALL SELECT SUM(EXTN_CHARGE) FROM EXTENSION WHERE switch_ext = '508' and site = 'BOS' and pin = '' =============================================== ...

  1. #1

    Default how to keep sum() from rounding

    The first statement rounds to two decimal places, if I
    remove the sum ( after the union)
    (second statement) NO rounding occurs. Anyone know of a
    way to keep it from rounding and still sum the number?

    /////////////////////////////////////////////

    SELECT sum((calls.rated_toll_cost +
    calls.tax_5_amount) -
    calls.call_volume_discount_amount *
    call_cost_factor)
    FROM (calls join cost_groups on cost_group_nbr =
    call_cost_group and report_group = 'all') join
    organization on organization.organization =
    calls.organization
    WHERE switch_ext = '508' and site = 'BOS' and
    call_cost_group = '16' and
    calls.organization is not null

    UNION ALL

    SELECT SUM(EXTN_CHARGE)
    FROM EXTENSION
    WHERE switch_ext = '508' and site = 'BOS' and pin = ''
    ===============================================
    SELECT sum((calls.rated_toll_cost +
    calls.tax_5_amount) -
    calls.call_volume_discount_amount *
    call_cost_factor)
    FROM (calls join cost_groups on cost_group_nbr =
    call_cost_group and report_group = 'all') join
    organization on organization.organization =
    calls.organization
    WHERE switch_ext = '508' and site = 'BOS' and
    call_cost_group = '16' and
    calls.organization is not null

    UNION ALL

    SELECT EXTN_CHARGE
    FROM EXTENSION
    WHERE switch_ext = '508' and site = 'BOS' and pin = ''


    Robert Guest

  2. #2

    Default Re: how to keep sum() from rounding

    Robert,

    SUM should sum any numeric, but there are a few limits on what it returns.
    See the BOL on the SUM function for those.

    In your case, perhaps doing a CAST of the number prior to summing would give
    your more success. For example:

    SELECT SUM(CAST (EXTN_CHARGE AS NUMERIC(15,5)))

    Russell Fields


    "Robert" <com> wrote in message
    news:10ad01c3619c$38aa6770$gbl... 


    Russell Guest

  3. #3

    Default Re: how to keep sum() from rounding

    Yes thanks , that's exactly what I ended up doing to solve
    the problem.
     
    what it returns. 
    summing would give 
    >
    >
    >.
    >[/ref]
    Robert Guest

Similar Threads

  1. rounding
    By GeorgeWS in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 10th, 09:05 AM
  2. Always Rounding Up
    By Barbara in forum FileMaker
    Replies: 4
    Last Post: February 11th, 12:53 AM
  3. Rounding off
    By Mike Blezien in forum PERL Beginners
    Replies: 1
    Last Post: November 22nd, 11:22 PM
  4. Rounding Up
    By Brent Bortnick in forum ASP
    Replies: 14
    Last Post: October 8th, 04:19 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