# Total Count

• ### 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

• ```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 Tuesday, July 29, 2014 1:59 PM
• Marked as answer by 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

```

• Proposed as answer by Thursday, August 7, 2014 1:20 PM
• Marked as answer by 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 Thursday, August 7, 2014 1:20 PM
• Marked as answer by 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 Tuesday, July 29, 2014 1:59 PM
• Marked as answer by 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

```

• Proposed as answer by Thursday, August 7, 2014 1:20 PM
• Marked as answer by 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 Thursday, August 7, 2014 1:20 PM
• Marked as answer by Friday, August 8, 2014 5:53 AM
Tuesday, July 29, 2014 2:13 PM