Solved

Display Percentage of total for each record using TSQL

Posted on 2014-10-09
5
129 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40371782
Use the following:

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

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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