locked
Total Count RRS feed

  • Question

  • Hi all, I have a complex requirement, not sure if its possible.

    DECLARE @temp table (
    ID int,
    Amount decimal (18,0)

    )

    INSERT INTO @temp
    SELECT 1, 100.00
    INSERT INTO @temp
    SELECT 2, 100.00
    INSERT INTO @temp
    SELECT 3, 200.00
    INSERT INTO @temp
    SELECT 4, 50.00
    INSERT INTO @temp
    SELECT 5, 50.00


    SELECT * FROM @temp

    This returns the below
    ID      Amount
    1      100
    2      100
    3      200
    4      50
    5      50

    The Total sum is 500


    I would like the top 3 which I know I can do by order by desc amount and selecting Select top 3. but what i want is this new column which divides the indivdual amount of 100 for example with the total from the recordset....

    ID      Amount   TotalPer
    1         100       0.2 --100/500
    2         100       0.2 --100/500
    3         200       0.4 --200/500

    Tuesday, July 29, 2014 1:47 PM

Answers

  • DECLARE @temp table (
    ID int, 
    Amount decimal (18,0)
    
    )
    
    INSERT INTO @temp
    SELECT 1, 100.00
    INSERT INTO @temp
    SELECT 2, 100.00
    INSERT INTO @temp
    SELECT 3, 200.00
    INSERT INTO @temp
    SELECT 4, 50.00
    INSERT INTO @temp
    SELECT 5, 50.00
    
    
    ;with mycte as (SELECT *, row_number() Over(Order by id ) rn,  sum(Amount) Over() total 
    --sum(Amount) Over(Order by ID)
    FROM @temp)
    
    Select ID, Amount, Cast(Amount*1./total as decimal(6,1)) TotalPer from mycte
    WHERE rn<4

    • Proposed as answer by Nico Boey Tuesday, July 29, 2014 1:59 PM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:53 AM
    Tuesday, July 29, 2014 1:54 PM
  • Try this:

    DECLARE @temp table (
     ID int, 
    Amount decimal (18,0)
     
    )
     
    INSERT INTO @temp
     SELECT 1, 100.00
     INSERT INTO @temp
     SELECT 2, 100.00
     INSERT INTO @temp
     SELECT 3, 200.00
     INSERT INTO @temp
     SELECT 4, 50.00
     INSERT INTO @temp
     SELECT 5, 50.00
     
    DECLARE @S INT
    SET @S = (SELECT SUM(Amount) amt FROM @temp)
    
    SELECT TOP 3 *, CAST((Amount/@S) AS NUMERIC(5,1)) TotalPer
    FROM @temp
    ORDER BY ID
    
    
    
    
    


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Proposed as answer by tracycai Thursday, August 7, 2014 1:20 PM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:53 AM
    Tuesday, July 29, 2014 2:03 PM
  • DECLARE @forumTable TABLE (id INT, amount INT)
    INSERT INTO @forumTable (id, amount)
    VALUES
    (1,100),(2,100),(3,200),(4,50),(5,50)
    
    SELECT TOP 3 d.id, d.amount, t.total, (.0+amount)/total AS pctTotal
      FROM @forumTable d
        INNER JOIN (SELECT SUM(amount) total FROM @forumTable) t
    	  ON t.total = t.total

    • Proposed as answer by tracycai Thursday, August 7, 2014 1:20 PM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:53 AM
    Tuesday, July 29, 2014 2:13 PM

All replies

  • DECLARE @temp table (
    ID int, 
    Amount decimal (18,0)
    
    )
    
    INSERT INTO @temp
    SELECT 1, 100.00
    INSERT INTO @temp
    SELECT 2, 100.00
    INSERT INTO @temp
    SELECT 3, 200.00
    INSERT INTO @temp
    SELECT 4, 50.00
    INSERT INTO @temp
    SELECT 5, 50.00
    
    
    ;with mycte as (SELECT *, row_number() Over(Order by id ) rn,  sum(Amount) Over() total 
    --sum(Amount) Over(Order by ID)
    FROM @temp)
    
    Select ID, Amount, Cast(Amount*1./total as decimal(6,1)) TotalPer from mycte
    WHERE rn<4

    • Proposed as answer by Nico Boey Tuesday, July 29, 2014 1:59 PM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:53 AM
    Tuesday, July 29, 2014 1:54 PM
  • Try this:

    DECLARE @temp table (
     ID int, 
    Amount decimal (18,0)
     
    )
     
    INSERT INTO @temp
     SELECT 1, 100.00
     INSERT INTO @temp
     SELECT 2, 100.00
     INSERT INTO @temp
     SELECT 3, 200.00
     INSERT INTO @temp
     SELECT 4, 50.00
     INSERT INTO @temp
     SELECT 5, 50.00
     
    DECLARE @S INT
    SET @S = (SELECT SUM(Amount) amt FROM @temp)
    
    SELECT TOP 3 *, CAST((Amount/@S) AS NUMERIC(5,1)) TotalPer
    FROM @temp
    ORDER BY ID
    
    
    
    
    


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Proposed as answer by tracycai Thursday, August 7, 2014 1:20 PM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:53 AM
    Tuesday, July 29, 2014 2:03 PM
  • DECLARE @forumTable TABLE (id INT, amount INT)
    INSERT INTO @forumTable (id, amount)
    VALUES
    (1,100),(2,100),(3,200),(4,50),(5,50)
    
    SELECT TOP 3 d.id, d.amount, t.total, (.0+amount)/total AS pctTotal
      FROM @forumTable d
        INNER JOIN (SELECT SUM(amount) total FROM @forumTable) t
    	  ON t.total = t.total

    • Proposed as answer by tracycai Thursday, August 7, 2014 1:20 PM
    • Marked as answer by Sofiya Li Friday, August 8, 2014 5:53 AM
    Tuesday, July 29, 2014 2:13 PM