[SQL Server] How Sum and Group By with name in storeproceure? pls help me T_T.

• ppc493

  SSC Veteran

  Points: 290

  More actions

  August 15, 2012 at 10:17 pm

  #279836

  I want sum money. but i want group name and code in status.

  that's code stroproceure [Sql Server 2008]:

  SELECT um.upmoney as 'money'

  ,um.pId as 'code',um.FName as 'name',up.status as 'statusmoney'

  From tb_upmoney um inner join tb_pID up on up.uId=um.pId

  Result:

  money | code | name | statusmoney

  200.00 | 00001 | fin | 1

  100.00 | 00001 | fin | 1

  50.00 | 00001 | fin | 1

  100.00 | 00002 | welson | 1

  200.00 | 00002 | welson | 2

  100.00 | 00002 | welson | 2

  50.00 | 00002 | welson | 2

  0.00 | 00002 | welson | 2

  but i want result:

  money | code | name | statusmoney

  250.00 | 00001 | fin | 1

  100.00 | 00002 | welson | 1

  250.00 | 00002 | welson | 2

  please help me. i'm start learning stroproceure it's Not good code sql.

  Thank Regards. 🙂

• deepzzzz

  SSCrazy

  Points: 2890

  More actions

  August 15, 2012 at 10:32 pm

  #1526323

  Why you discarded these rows?

  100.00 | 00001 | fin | 1

  100.00 | 00002 | welson | 2

  In what sense, you are grouping and adding the data?

  Thanks

• gilding

  SSC Enthusiast

  Points: 141

  More actions

  August 15, 2012 at 10:34 pm

  #1526324

  Hello.

  Solution is easy and look like this:

  select um.upmoney as 'money'

  , um.pId as 'code'

  , um.FName as 'name'

  , up.status as 'statusmoney'

  from tb_upmoney um

  inner join tb_pID up on up.uId=um.pId

  group by um.pId, um.FName, up.status

  -- aslo you can use window function

  select sum( um.upmoney ) over (partition by um.pId, um.FName, up.status ) as 'money'

  , um.pId as 'code'

  , um.FName as 'name'

  , up.status as 'statusmoney'

  from tb_upmoney um

  inner join tb_pID up on up.uId=um.pId