CODEWITHSUNDEEP
sqlpercentage
Joaquim
Joaquim

Reputation: 57

SQL percentage of the total

Hi how can I get the percentage of each record over the total? Lets imagine I have one table with the following

ID code Points
1   101  2
2   201  3
3   233  4
4   123  1

The percentage for ID 1 is 20% for 2 is 30% and so one how do I get it?

Upvotes: 4

Views: 6631

Answers (3)

spencer7593
spencer7593

Reputation: 108430

There's a couple approaches to getting that result.

You essentially need the "total" points from the whole table (or whatever subset), and get that repeated on each row. Getting the percentage is a simple matter of arithmetic, the expression you use for that depends on the datatypes, and how you want that formatted.

Here's one way (out a couple possible ways) to get the specified result:

SELECT t.id
     , t.code
     , t.points
  -- , s.tot_points
     , ROUND(t.points * 100.0 / s.tot_points,1) AS percentage
  FROM onetable t
 CROSS
  JOIN ( SELECT SUM(r.points) AS tot_points
           FROM onetable r
       ) s
 ORDER BY t.id

The view query s is run first, that gives a single row. The join operation matches that row with every row from t. And that gives us the values we need to calculate a percentage.

Another way to get this result, without using a join operation, is to use a subquery in the SELECT list to return the total.

Note that the join approach can be extended to get percentage for each "group" of records.

id  type    points   %type
--  ----    ------   -----
1   sold        11     22%
2   sold         4      8%
3   sold        25     50% 
4   bought       1   50%
5   bought       1   50%
6   sold        10     20%

To get that result, we can use the same query, but a a view query for s that returns total GROUP BY r.type, and then the join operation isn't a CROSS join, but a match based on type:

SELECT t.id
     , t.type
     , t.points
  -- , s.tot_points_by_type
     , ROUND(t.points * 100.0 / s.tot_points_by_type,1) AS `%type`
  FROM onetable t
  JOIN ( SELECT r.type
              , SUM(r.points) AS tot_points
           FROM onetable r
          GROUP BY r.type
       ) s
    ON s.type = t.type
 ORDER BY t.id

To do that same result with the subquery, that's going to be a correlated subquery, and that subquery is likely to get executed for every row in t.

This is why it's more natural for me to use a join operation, rather than a subquery in the SELECT list... even when a subquery works the same. (The patterns we use for more complex queries, like assigning aliases to tables, qualifying all column references, and formatting the SQL... those patterns just work their way back into simple queries. The rationale for these patterns is kind of lost in simple queries.)

Upvotes: 4

Kahn
Kahn

Reputation: 1660

To add to a good list of responses, this should be fast performance-wise, and rather easy to understand:

DECLARE @T TABLE (ID INT, code VARCHAR(256), Points INT)

INSERT INTO @T VALUES (1,'101',2), (2,'201',3),(3,'233',4), (4,'123',1)

;WITH CTE AS
    (SELECT * FROM @T)
SELECT C.*, CAST(ROUND((C.Points/B.TOTAL)*100, 2) AS DEC(32,2)) [%_of_TOTAL]
FROM CTE C
JOIN (SELECT CAST(SUM(Points) AS DEC(32,2)) TOTAL FROM CTE) B ON 1=1

Just replace the table variable with your actual table inside the CTE.

Upvotes: 0

Sathish
Sathish

Reputation: 4487

try like this

select id,code,points,(points * 100)/(select sum(points) from tabel1) from table1

Upvotes: 2

Related Questions