CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
mgabriel
mgabriel

Reputation: 155

SQL Server : Calculate a percentual value in a row out of a sum of multiple rows

I need to get a percentage of a single value out of a sum of multiple rows in SQL Server.

Example rows:

id   name    value    (% of sum of matching rows)
-------------------------------------------------
1    kermit    100    (16,67%)
2    piggy     200    (33,33%)
3    tiffy     300    (50,00%)
-------------------------------------------------
               600    (100%)

I tried some subqueries, aggregations with sum(), but I could not find any way how to determine the percentage of the value within a sum of certain rows matching a criteria.

(updated to be more specific)

Upvotes: 0

Views: 191

Answers (2)

M.Ali
M.Ali

Reputation: 69524

Test Data

DECLARE @TABLE TABLE (id INT,name VARCHAR(100),value INT)
INSERT INTO @TABLE VALUES    
(1,'kermit',100),(2,'piggy',200),(3,'tiffy',300)

Query

;WITH CTE1
AS 
 (
  SELECT SUM(value) AS Total
  FROM @TABLE
  ),
CTE2
AS
  (
  SELECT *
    , CAST(CAST((CAST(Value AS NUMERIC(10,2)) /
       (SELECT CAST(Total AS NUMERIC(10,2)) FROM CTE1)) * 100.00
        AS NUMERIC(4,2)) AS NVARCHAR(10)) + '%' AS [% of sum of matching rows]
  FROM @TABLE
  )
SELECT * 
FROM CTE2

Result Set

╔════╦════════╦═══════╦═══════════════════════════╗
║ id ║  name  ║ value ║ % of sum of matching rows ║
╠════╬════════╬═══════╬═══════════════════════════╣
║  1 ║ kermit ║   100 ║ 16.67%                    ║
║  2 ║ piggy  ║   200 ║ 33.33%                    ║
║  3 ║ tiffy  ║   300 ║ 50.00%                    ║
╚════╩════════╩═══════╩═══════════════════════════╝

Upvotes: 2

Brian DeMilia
Brian DeMilia

Reputation: 13248

Where "tbl" is the name of your table and "fieldvalue" is the field in question:

select tbl.*, tbl.fieldvalue/sub.tot as pct_of_all
from tbl, (select sum(fieldvalue) as tot from tbl) sub

This assumes for "all rows" you mean all the rows on the table and not only those meeting a certain criteria or grouped in any way.

Based on your edit, use the where clause both in the inline view in the from clause and also in the where clause of the outer layer of the query to filter both the values being compared and also the total (the divisor) with which it's being compared, like so: (in this example, where x = 2 is the criteria):

select tbl.*, tbl.fieldvalue/sub.tot as pct_of_all
from tbl, (select sum(fieldvalue) as tot from tbl where x = 2) sub
where x = 2

Upvotes: 1

Related Questions