none
Calculate sum based on another column RRS feed

  • Question

  • how to calculate sum of a column values based on another column value without creating view or function in a sql server.

    EX:

    Col 1 Col 2   Col 3 = Col2/Sum of same value from col1
      
    A      1          1/3
    A      2          2/3
    B      3          3/12
    B      4         4/12
    B      5         5/12
    C      6         6/13
    C      7         7/13

    any suggestions?

    Thursday, May 30, 2013 9:47 PM

Answers

  • pls try

    create table #t (c char(1),c1 int)
    insert into #t(c,c1) values
    ('A',1),    
    ('A',2),   
    ('B',3),    
    ('B',4),   
    ('B',5),   
    ('C',6),   
    ('C',7)   
    
    select c,c1,c1*1.0/ sum(c1) over(partition by c)
    from #t

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by VPalyam Thursday, May 30, 2013 10:25 PM
    Thursday, May 30, 2013 10:01 PM

All replies

  • pls try

    create table #t (c char(1),c1 int)
    insert into #t(c,c1) values
    ('A',1),    
    ('A',2),   
    ('B',3),    
    ('B',4),   
    ('B',5),   
    ('C',6),   
    ('C',7)   
    
    select c,c1,c1*1.0/ sum(c1) over(partition by c)
    from #t

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by VPalyam Thursday, May 30, 2013 10:25 PM
    Thursday, May 30, 2013 10:01 PM
  • Thanks vt but I have only read access to this database, I cannot create any table, view or function
    Thursday, May 30, 2013 10:08 PM
  • thats just an e.g. code only, replace c and c1 with actual column name and #t with actual table name



    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by VPalyam Thursday, May 30, 2013 10:25 PM
    • Unmarked as answer by VPalyam Thursday, May 30, 2013 10:25 PM
    Thursday, May 30, 2013 10:12 PM
  • try this code ,

    declare @tab table(id varchar , value int)
    insert into @tab values('A',1)
    insert into @tab values('A',2)

    insert into @tab values('B',3)
    insert into @tab values('B',4)
    insert into @tab values('B',5)

    insert into @tab values('C',6)
    insert into @tab values('C',7)

    select t1.id ,t2.value ,cast(t2.value as decimal)/cast(t1.SUM as decimal) from @tab t2  inner join
     (select id , SUM(value) as SUM   from @tab group by id)  t1 on t1.id = t2.id

    Thursday, May 30, 2013 10:17 PM
  • Thanks Vt, was not aware of over clause and now that I know used it works perfect .
    Thursday, May 30, 2013 10:28 PM
  • Try this

    CREATE TABLE #t (c CHAR(1),c1 INT)
    INSERT INTO #t(c,c1) VALUES
    ('A',1),    
    ('A',2),   
    ('B',3),    
    ('B',4),   
    ('B',5),   
    ('C',6),   
    ('C',7) 

    ;WITH c1 AS
    (
    SELECT c,SUM(c1) AS c1
    FROM #t
    GROUP BY c
    )

    SELECT c.c,t.c1,c.c1,CAST(t.c1 AS DECIMAL(8,2))/CAST(c.c1 AS DECIMAL(8,2)) as'%'
    FROM c1 c
    INNER JOIN #t t
    ON c.c = t.c

    Thursday, May 30, 2013 11:33 PM