Find total and percentage RRS feed

  • Question

  • Hi Team,

    I have requirement as below.

       col A            Col b          SUMcol              %col

    category 1       1500                               

    category 2        2000                           

    category 3       800        



    category 6        1000 

    So I want to calculate SUMCol as sum(col b) i,e->1500+2000+800+. ..+1000 = TOTAL VALUE

    Then i want to calculate % col as  colb/Sumcol*100 for each row.-->1500/total value*100  , 200/total value*100 and so on..

    How can I do these at query level. 

    Kindly help

    Thank you


    Thursday, March 21, 2019 2:35 PM

All replies

  • CREATE TABLE #tmp (ColA VARCHAR(20), ColB INT)
    INSERT INTO #tmp VALUES ('category1',1500), ('category2',2000), ('category3',800), ('category6',1000)
    SELECT ColA, ColB, SUM(ColB) OVER() SumCol,  CAST((ColB*1.0/SUM(ColB) OVER())*100 AS DECIMAL(4,2)) AS '%col'
    FROM #tmp
    DROP TABLE #tmp

    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, March 21, 2019 2:42 PM
  • select [col a],[col b], sum ([col b]), avg[(sum([col b])/6)*100] as avg from table

    Thursday, March 21, 2019 2:47 PM
  • If you want your SUMCol as running total, you change Ousama's query a little:

    SELECT ColA, ColB, SUM(ColB) OVER(Order by ColA) SumCol,  CAST((ColB*1.0/SUM(ColB) OVER())*100 AS DECIMAL(4,2)) AS '%col'
    FROM #tmp

    Thursday, March 21, 2019 3:32 PM