Post reply

Aggregate

  • July 17, 2012 at 1:09 pm

    #264309

    Hi All,

    This is my sample data

    Composite_id Index Value

    MX0493 idx000022 15000

    MX0493 idx000023 14000

    MX0493 idx000028 9000

    MX0493 idx000046 2000

    MX0493 idx000049 9000

    MX0493 idx000050 8000

    MX0493 idx000051 8000

    MX0493 idx000091 23000

    MX0493 idx000091 4000

    MX0493 idx000091 3000

    MX0493 idx000112 5000

    I want data to be like this , it will aggregate wherever we have same index, for example IDX000091 will

    aggregated and value will be 30000

    Composite_id index value

    MX0493 idx000022 15000

    MX0493 idx000023 14000

    MX0493 idx000028 9000

    MX0493 idx000046 2000

    MX0493 idx000049 9000

    MX0493 idx000050 8000

    MX0493 idx000051 8000

    MX0493 idx000091 30000

    MX0493 idx000112 5000

    Any help is appreciated.

    Thanks,

    Nick

  • July 17, 2012 at 1:22 pm

    #1513837

    Try this:

    DECLARE @testagg TABLE

    (

    id VARCHAR(10),

    nm VARCHAR(20),

    amt INT

    )

    INSERT @testagg

    SELECT 'MX0493',

    'idx000022',

    15000

    UNION ALL

    SELECT 'MX0493',

    'idx000023',

    14000

    UNION ALL

    SELECT 'MX0493',

    'dx000028',

    9000

    UNION ALL

    SELECT 'MX0493',

    'idx000046',

    2000

    UNION ALL

    SELECT 'MX0493',

    'idx000049',

    9000

    UNION ALL

    SELECT 'MX0493',

    'idx000050',

    8000

    UNION ALL

    SELECT 'MX0493',

    'idx000051',

    8000

    UNION ALL

    SELECT 'MX0493',

    'idx000091',

    23000

    UNION ALL

    SELECT 'MX0493',

    'idx000091',

    4000

    UNION ALL

    SELECT 'MX0493',

    'idx000091',

    3000

    UNION ALL

    SELECT 'MX0493',

    'idx000112',

    5000

    SELECT id,

    nm,

    Sum(amt)

    FROM @testagg

    GROUP BY id,

    nm

  • July 17, 2012 at 1:33 pm

    #1513842

    Thanks for the quick reply

    but i am getting error

    Msg 8117, Level 16, State 1, Line 56

    Operand data type char is invalid for sum operator.

    when I am trying to use logic in the query:

    SELECT id,

    nm,

    --Sum(amt) ,

    sum (CAST(REPLICATE(' ',16-LEN(AMT*1000)) + CONVERT(varchar(32),AMT*1000) as CHAR(16)))as value

    FROM @testagg

    GROUP BY id,

    nm

  • July 17, 2012 at 1:46 pm

    #1513852

    You must do the sum before you cast your values.

    SELECT id,

    nm,

    CAST(REPLICATE(' ',16-LEN(SUM(AMT*1000))) + CONVERT(varchar(32),SUM(AMT*1000)) as CHAR(16))

    FROM @testagg

    GROUP BY id,

    nm

    Why would you like to do it in SQL and not in the front end?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 17, 2012 at 2:24 pm

    #1513883

    Using a CTE, I think this is a little cleaner.

    WITH BaseData AS (

    SELECT

    id,

    nm,

    SUM(AMT*1000) AS value

    FROM

    @testagg

    GROUP BY

    id,

    nm

    )

    SELECT

    id,

    nm,

    RIGHT(REPLICATE(' ',16) + CAST(VALUE AS VARCHAR(16)), 16) AS value

    FROM

    BaseData

    ORDER BY

    id,

    nm;

  • July 18, 2012 at 8:19 am

    #1514245

    Thanks a lot Guys !!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply