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...

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...

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

```
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
```

```
| 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 |
```

```
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
```

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialCREATE TABLE TEST_TYPE (Type VARCHAR(10), Sum_A INT, Sum_B INT)

INSERT INTO TEST_TYPE VALUES('A',15,16),('B',5,7

SELECT TYPE

, SUM_A, CONVERT(VARCHAR,CONVERT(DE

, SUM_B, CONVERT(VARCHAR,CONVERT(DE

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

Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

SUM_A / Sum(SUM_A) OVER()