Results 1 to 3 of 3

Thread: aggregate values !!!!!!!

  1. #1
    Join Date
    Sep 2002
    Posts
    218

    aggregate values !!!!!!!

    --- Sum All Receipts and returns and Group by Period
    l'm running this script and my total is just not adding up. if l have -120 in 200102
    and -234 in 200210 my total is wrong.what am l doing wrong ? receipts have to be +ve and returns -Ve.. Please assist


    Select
    Case
    When LPR312 LIKE 'E%' Then 'PAYROLL BACKED BY D/O'
    When LPR312 LIKE 'NL%'Then 'NU - PAY'
    When LPR312 LIKE 'JL9%'Then 'SBSA JV'
    When LPR312 LIKE 'JL0%'Then 'E - PLAN'
    Else 'Other' End Product

    ,Case When AMT151 < 0 Then -AMT151
    Else 0 End As Receipts
    ,Case When AMT151 > 0 Then -AMT151
    Else 0 End As Returns

    ,Sum(Case Period When 200201 Then AMT151 Else 0 End) As '200201'
    ,Sum(Case Period When 200202 Then AMT151 Else 0 End) As '200202'
    ,Sum(Case Period When 200203 Then AMT151 Else 0 End) As '200203'
    ,Sum(Case Period When 200204 Then AMT151 Else 0 End) As '200204'

    ,Sum(Case Period When 200205 Then AMT151 Else 0 End) As '200205'
    ,Sum(Case Period When 200206 Then AMT151 Else 0 End) As '200206'
    ,Sum(Case Period When 200207 Then AMT151 Else 0 End) As '200207'
    ,Sum(Case Period When 200208 Then AMT151 Else 0 End) As '200208'

    ,Sum(Case Period When 200209 Then AMT151 Else 0 End) As '200209'
    ,Sum(Case Period When 200210 Then AMT151 Else 0 End) As '200210'
    ,Sum(Case Period When 200211 Then AMT151 Else 0 End) As '200211'
    ,Sum(Case Period When 200212 Then AMT151 Else 0 End) As '200212'
    ,Sum(AMT151) AS Total
    ,Count(*) As RecordCount
    From Import
    Group By LPR312,Amt151

  2. #2
    Join Date
    Jan 2003
    Posts
    2
    Hi,
    I don't quite see what u r trying to achieve, but there is something wrong with your group by :
    Group By LPR312,Amt151
    I cannot see why u r grouping by amt151 as your r also summing up its value.

  3. #3
    Join Date
    Dec 2002
    Posts
    181
    It looks like you are trying to create a pivot table.
    You state that receipts have to be positive however in this statement:

    Case When AMT151 < 0 Then -AMT151
    Else 0 End As Receipts

    you seem to be forcing the column to a negative value? Can you post some DDL as to better clarify the problem?

    Jeff

Posting Permissions

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