Solved

Display Percentage of total for each record using TSQL

Posted on 2014-10-09
5
125 Views
Last Modified: 2014-10-15
Hello Experts,

I have the following table:

Type      Sum_A      Sum_B            
A                  15                16            
B                    5                  7            
C                    6                  8            
D                    0                49            
      
The total for Sum_A = 26      
The total for Sum_B = 80      

I would like to create a view that displays the % of each Sum_A and Sum_B record to the totals of Sum_A and Sum_B.
It should like this below:

Type      Sum_A      %Tot_A      Sum_B      %Tot_B
A                 15       58%          16                20%
B                   5       19%           7                  9%
C                   6         23%          8                10%
D                   0         0%          49                 61%

For example:
The %Tot_A (for Type A) = 15/26 = 58%
The %Tot_A (for Type B) = 5/26 = 19%
The %Tot_A (for Type C) = 6/26 = 23%
The %Tot_A (for Type D) = 0/26 = 0%

Same for %Tot_B...
0
Comment
Question by:Saxitalis
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40371782
Use the following:

SUM_A / Sum(SUM_A) OVER()
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40372421
You can use a common table expression here (CTE):
CREATE VIEW vPercent AS
WITH Total_CTE (TotalA, TotalB)
AS (SELECT SUM(Sum_A)*1.0, SUM(Sum_B)*1.0  
FROM YourTableNameHere)
SELECT A.Type, Sum_A, 100*A.Sum_A/(SELECT Sum_A FROM Total_CTE) AS '%Tot_A',
                            Sum_B, 100*A.Sum_B/(SELECT Sum_B FROM Total_CTE) AS '%Tot_B',
FROM YourTableNameHere

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40372691
| TYPE | SUM_A | %TOT_A | SUM_B | %TOT_B |
|------|-------|--------|-------|--------|
|    A |    15 |  57.69 |    16 |     20 |
|    B |     5 |  19.23 |     7 |   8.75 |
|    C |     6 |  23.08 |     8 |     10 |
|    D |     0 |      0 |    49 |  61.25 |

Open in new window


select 
        type
      , sum_a
      , round( (sum_a * 100.0) / sum(sum_a) over() ,2) "%Tot_A"
      , sum_b
      , round( (sum_b * 100.0) / sum(sum_b) over() ,2) "%Tot_B"
from table1

Open in new window

0
 

Expert Comment

by:Raja Sekhar Reddy
ID: 40381804
This can be done using window functions in sql server.

CREATE TABLE TEST_TYPE (Type   VARCHAR(10),     Sum_A  INT,      Sum_B  INT)
INSERT INTO TEST_TYPE VALUES('A',15,16),('B',5,7),('C',6,8),('D',0,49)


SELECT TYPE
     , SUM_A,  CONVERT(VARCHAR,CONVERT(DECIMAL(5,1),100.0 * SUM_A / SUM(sum_a) OVER())) as Percent_A
     , SUM_B, CONVERT(VARCHAR,CONVERT(DECIMAL(5,1),100.0 *SUM_B/SUM(sum_b) OVER())) AS Percent_B
  FROM TEST_TYPE;

TYPE      SUM_A      Percent_A      SUM_B      Percent_B
A      15      57.7      16      20.0
B      5      19.2      7      8.8
C      6      23.1      8      10.0
D      0      0.0      49      61.3
0
 

Author Closing Comment

by:Saxitalis
ID: 40381967
This works great - Thanks!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now