Row wise sum based on condition

• LearnSQL

  SSC-Addicted

  Points: 407

  More actions

  May 17, 2022 at 3:51 am

  #4034046

  Hi All,

  I have an input like below.

  mytable: 
  
  id | name | no 
  --------------
  1  |  A   | 10
  1  |  A   | 20
  1  |  A   | 40
  2  |  B   | 20
  2  |  B   | 20
  1  |  C   | 20
  1  |  C   | 23
  3  |  D   | 15
  3  |  D   | 15

  Need the output like below, how to achieve this , Sum of no based on id, name needs to result in Sumdat

  for eg

  id=1 and name =A and no we have 10+20+40=70 = Sumdat column

  id=3 and name =D and no we have 15+15=30 = Sumdat column

  mytable: 
  
  id | name | no | SumDat
  ----------------------
  1  |  A   | 10  | 70
  1  |  A   | 20  | 70
  1  |  A   | 40  | 70
       
  2  |  B   | 20  | 40
  2  |  B   | 20  | 40
  
  1  |  C   | 20  | 43
  1  |  C   | 23  | 43
  
  3  |  D   | 15  | 30
  3  |  D   | 15  | 30

  Thanks !

• ktflash

  Default port

  Points: 1400

  More actions

  May 17, 2022 at 4:23 am

  #4034069

  select id
        ,name
        ,no
        ,sum(no) OVER(PARTITION BY id, name)
  from mytable

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help