Professional Web Applications Themes

Getting a SUM - Microsoft SQL / MS SQL Server

You can use Rollup operator to get the sum of the dollar_payout_1yr as a new row. select Tiers_Payout, Sum(Dollar_Payout_1YR) as Dollar_Payout_1YR from ( SELECT CAST(Tier_Level as varchar(2)) + ': ' + Tier_Desc AS Tiers_Payout, (Count(Access_Rate) - IsNull(Deact_Amount, 0)) * Tier_Payout AS Dollar_Payout_1YR FROM #TempCompEst WHERE Contract_Length = 1 GROUP BY Tier_Level, Tier_Desc, Deact_Amount, Tier_Payout )A group by Tiers_Payout WITH ROLLUP output ------- Tiers_Payout Dollar_Payout_1YR -------------------- --------------------- 1: $0 - $34.99 2.0000 1: [FREEUP] -5.0000 2: $35 - $44.99 .0000 3: $45 - $54.99 .0000 4: $55+ -96.0000 NULL -99.0000 HTH, Praveen Maddali MCSD, MCDBA "MEM" <net> wrote in message news:google.com... ...

  1. #1

    Default Re: Getting a SUM

    You can use Rollup operator to get the sum of the dollar_payout_1yr as a new
    row.



    select Tiers_Payout,
    Sum(Dollar_Payout_1YR) as Dollar_Payout_1YR
    from
    (
    SELECT CAST(Tier_Level as varchar(2)) + ': ' + Tier_Desc AS
    Tiers_Payout, (Count(Access_Rate) - IsNull(Deact_Amount, 0)) *
    Tier_Payout AS Dollar_Payout_1YR
    FROM #TempCompEst WHERE Contract_Length = 1
    GROUP BY Tier_Level, Tier_Desc, Deact_Amount, Tier_Payout
    )A
    group by Tiers_Payout WITH ROLLUP

    output
    -------

    Tiers_Payout Dollar_Payout_1YR
    -------------------- ---------------------
    1: $0 - $34.99 2.0000
    1: [FREEUP] -5.0000
    2: $35 - $44.99 .0000
    3: $45 - $54.99 .0000
    4: $55+ -96.0000
    NULL -99.0000


    HTH,
    Praveen Maddali
    MCSD, MCDBA

    "MEM" <net> wrote in message
    news:google.com... 


    praveen Guest

  2. #2

    Default Re: Getting a SUM


    select Sum(Dollar_Payout_1YR) as Total_Dollar_Payout_1YR
    from
    (
    SELECT CAST(Tier_Level as varchar(2)) + ': ' + Tier_Desc AS
    Tiers_Payout, (Count(Access_Rate) - IsNull(Deact_Amount, 0)) *
    Tier_Payout AS Dollar_Payout_1YR
    FROM #TempCompEst WHERE Contract_Length = 1
    GROUP BY Tier_Level, Tier_Desc, Deact_Amount, Tier_Payout
    ) A

    Praveen


    "MEM" <net> wrote in message
    news:google.com... 
    news:<phx.gbl>... [/ref]
    new [/ref][/ref]


    praveen Guest

  3. #3

    Default Re: Getting a SUM

    Thanks Again! Very helpful!!

    "praveen" <stph.net> wrote in message news:<OMv#phx.gbl>... 
    > news:<phx.gbl>... [/ref]
    > new [/ref][/ref]
    MEM Guest

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