Aggregate

• sqlenthu_11

  SSC Eights!

  Points: 800

  More actions

  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_idindexvalue

  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

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  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

• sqlenthu_11

  SSC Eights!

  Points: 800

  More actions

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  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;

• sqlenthu_11

  SSC Eights!

  Points: 800

  More actions

  July 18, 2012 at 8:19 am

  #1514245

  Thanks a lot Guys !!!!