, Count(a.*) AS CategoryCount

, (Count(a.*) * 100.0)/b.TotalCount AS CategoryRatio

FROM TableName a

OUTER APPLY (SELECT Count(*) As TotalCount FROM TableName) b

GROUP BY a.Category

UNION ALL

SELECT 'Total', COUNT(*), 100.0

FROM TableName

Solved

Posted on 2008-11-12

I've looked through several question/responses on this topic, and tried some of the solutions and have not been able to get this to work...

In Oracle, I would use ratio to report to calculate the percentage of the total that each category covers, to come up with a total pct of 100. Unfortunately I'm working in SQL Server 2000 and I've not found an easy way to do this.

I found some solutions in SQL cookbook but they involve selecting a specfic category, and I want this additional calculation/column for all categories and that is throwing me off.

I have the following table where 1st column is a category, the 2nd column is a count

I want to find and write to a 3rd column the value that comes from Cat A (61) / Total (37060) = 0.16%

and then I need a total line with the total count, and total %

I'm not opposed to adding a total to the original table and referencing that, although I think there is probably a cleaner way to do it.

Cat A 61

Cat B 298

Cat C 74

Cat D 1554

Cat E 191

Cat F 33057

Cat G 1825

The result I want:

Cat A 61 0.16

Cat B 298 0.80

Cat C 74 0.20

Cat D 1554 4.20

Cat E 191 0.52

Cat F 33057 89.29

Cat G 1825 4.93

Total 37020 100(%)

Thanks in advance!

In Oracle, I would use ratio to report to calculate the percentage of the total that each category covers, to come up with a total pct of 100. Unfortunately I'm working in SQL Server 2000 and I've not found an easy way to do this.

I found some solutions in SQL cookbook but they involve selecting a specfic category, and I want this additional calculation/column for all categories and that is throwing me off.

I have the following table where 1st column is a category, the 2nd column is a count

I want to find and write to a 3rd column the value that comes from Cat A (61) / Total (37060) = 0.16%

and then I need a total line with the total count, and total %

I'm not opposed to adding a total to the original table and referencing that, although I think there is probably a cleaner way to do it.

Cat A 61

Cat B 298

Cat C 74

Cat D 1554

Cat E 191

Cat F 33057

Cat G 1825

The result I want:

Cat A 61 0.16

Cat B 298 0.80

Cat C 74 0.20

Cat D 1554 4.20

Cat E 191 0.52

Cat F 33057 89.29

Cat G 1825 4.93

Total 37020 100(%)

Thanks in advance!

25 Comments

, Count(a.*) AS CategoryCount

, (Count(a.*) * 100.0)/b.TotalCount AS CategoryRatio

FROM TableName a

OUTER APPLY (SELECT Count(*) As TotalCount FROM TableName) b

GROUP BY a.Category

UNION ALL

SELECT 'Total', COUNT(*), 100.0

FROM TableName

I was trying the outer apply for performance reasons as I think that performs a little quicker, but basically this is my suggestion.

```
SELECT a.Category
, Count(a.*) AS CategoryCount
, (Count(a.*) * 100.0)/(SELECT Count(*) FROM TableName) AS CategoryRatio
FROM TableName a
GROUP BY a.Category
UNION ALL
SELECT 'Total', COUNT(*), 100.0
FROM TableName
-- You can try the WITH Rollup command as well:
SELECT a.Category
, Count(a.*) AS CategoryCount
, (Count(a.*) * 100.0)/(SELECT Count(*) FROM TableName) AS CategoryRatio
FROM TableName a
GROUP BY a.Category WITH ROLLUP
```

1) I'm getting errors when I swap in my table name #ytd8

SELECT a.Category,

Count(a.*) AS CategoryCount,

(Count(a.*) * 100.0)/b.TotalCount AS CategoryRatio

FROM #ytd8 a

OUTER APPLY (SELECT Count(*) As TotalCount FROM #ytd8) b

GROUP BY a.Category

UNION ALL

SELECT 'Total', COUNT(*), 100.0

FROM #ytd8

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near '*'.

Msg 170, Level 15, State 1, Line 5

Line 5: Incorrect syntax near 'b'.

2) the union statement 'Total' is only giving me a count of 8 instead of count of 37020

Total 8 100.0

It's a temp table, added up from several other tables

check the below query.

SELECT a.Category

,a.Count1

,a.Count1/SUM(a.Count1) AS Ratio

FROM #ytd8 a

GROUP BY a.Category, a.Count1

UNION ALL

SELECT 'Total' AS Category

,SUM(Count1) AS Count1

,100 AS Ratio

FROM #ytd8

SELECT a.Category

,a.count

,a.count/SUM(a.count) AS Ratio

FROM #ytd8 a

GROUP BY a.Category, a.count

returns:

cat1 61 1

cat 2 74 1

cat3 191 1

cat4 298 1

cat5 1554 1

cat6 1825 1

cat7 33057 1

Not really what I'm looking for, I need the percentages listed in the original posting

The values in the Count column are already totaled from another table, i just need to divide the count for each category by the total count (37020)

SELECT a.Category

,a.Count1

,CAST(a.Count1 AS MONEY)/CAST(SUM(a.Count1) AS MONEY) AS Ratio

FROM #ytd8 a

GROUP BY a.Category, a.Count1

UNION ALL

SELECT 'Total' AS Category

,SUM(Count1) AS Count1

,100 AS Ratio

FROM #ytd8

You will have to change column names, but here is what I did to test.

```
-- my test data --
CREATE TABLE #ytd8 (Category VARCHAR(50), CategoryCount INT)
INSERT INTO #ytd8
SELECT 'Cat A', 61
UNION ALL SELECT 'Cat B', 298
UNION ALL SELECT 'Cat C', 74
UNION ALL SELECT 'Cat D', 1554
UNION ALL SELECT 'Cat E', 191
UNION ALL SELECT 'Cat F', 33057
UNION ALL SELECT 'Cat G', 1825
-- my test data --
SELECT a.Category
, CategoryCount
, (CategoryCount * 100.0)/(SELECT SUM(CategoryCount) FROM #ytd8) AS CategoryRatio
FROM #ytd8 a
GROUP BY a.Category, a.CategoryCount
UNION ALL
SELECT 'Total', SUM(CategoryCount), 100.0
FROM #ytd8
```

I need to know the % that 61 makes up in the overall total (which is .16%)

ECT 61 1.00

Sub Acute 74 1.00

Mileage 191 1.00

Injections 298 1.00

Hospital Inpat1554 1.00

Inpatient 1825 1.00

Outpatient 33057 1.00

```
SELECT a.Category
, a.CategoryCount
, (a.CategoryCount * 100.0)/b.TotalCount AS CategoryRatio
FROM #ytd8 a
OUTER APPLY (SELECT SUM(CategoryCount) As TotalCount FROM #ytd8) b
UNION ALL
SELECT 'Total', SUM(CategoryCount), 100.0
FROM #ytd8
```

SELECT a.Category

,a.Count1

,(CAST(a.Count1 AS MONEY)/CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY) )AS Ratio

FROM #ytd8 a

GROUP BY a.Category, a.Count1

UNION ALL

SELECT 'Total' AS Category

,SUM(Count1) AS Count1

,100 AS Ratio

FROM #ytd8

,a.Count1

,ROUND(CAST(a.Count1 AS MONEY)/CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY),2) AS Ratio

FROM #ytd8 a

GROUP BY a.Category, a.Count1

UNION ALL

SELECT 'Total' AS Category

,SUM(Count1) AS Count1

,100.00 AS Ratio

FROM #ytd8

```
SELECT a.Category
, a.CategoryCount
, ROUND((a.CategoryCount * 100.0)/b.TotalCount, 2) AS CategoryRatio
FROM #ytd8 a
OUTER APPLY (SELECT SUM(CategoryCount) As TotalCount FROM #ytd8) b
UNION ALL
SELECT 'Total', SUM(CategoryCount), 100.00
FROM #ytd8
```

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'OUTER'.

Msg 170, Level 15, State 1, Line 5

Line 5: Incorrect syntax near 'b'.

Is there a way to do the rounding with the other code?

ECT 61 0.0016

Hospital Inpatient 1554 0.0419

Injections 298 0.008

Inpatient 1825 0.0492

Mileage 191 0.0051

Outpatient 33057 0.8919

Sub Acute 74 0.0019

Total 37060 100.00

SELECT a.Category

,a.Count1

,ROUND(CAST(a.Count1 AS MONEY)*100/CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY),2) AS Ratio

FROM #ytd8 a

GROUP BY a.Category, a.Count1

UNION ALL

SELECT 'Total' AS Category

,SUM(Count1) AS Count1

,100.00 AS Ratio

FROM #ytd8

ECT 61 0.1600

Hospital 1554 4.1900

Injections 298 0.8000

Inpatient 1825 4.9200

Mileage 191 0.5200

Outpatient 33057 89.2000

Sub Acute 74 0.2000

Total 37060 100.0000

SELECT a.Category

,a.Count1

,ROUND((CAST(a.Count1 AS MONEY) * 10000 / CAST((SELECT SUM(Count1) FROM #ytd8) AS MONEY))/100,2) AS Ratio

FROM #ytd8 a

GROUP BY a.Category, a.Count1

UNION ALL

SELECT 'Total' AS Category

,SUM(Count1) AS Count1

,100.00 AS Ratio

FROM #ytd8

ytdconsumers is what we referred to as "Count 1" throughout the process

consider ytdpaidamt as a "Count 2"

here is my table (without the totals), they were doing the totals in another step further down that I hadn't gotten to yet:

ECT 61 0.16 28146.64 0.09

Hospital In 1554 4.19 4214566.44 13.39

Injections 298 0.80 22326.24 0.07

Inpatient 1825 4.92 308814.30 0.98

Mileage 191 0.52 20253.47 0.06

Outpatient 33057 89.20 26819779.94 85.21

Sub Acute 74 0.20 62696.25 0.20

```
-- create table with ytd consumer pct
drop table #ytd8
SELECT Category,
ytdconsumers,
(ytdconsumers * 100.0)/(SELECT SUM(ytdconsumers) FROM #ytd7)AS ytd_pct,
consumers,
consumer_pct
into #ytd8
FROM #ytd7
order by category
-- create table with ytd amt paid pct
drop table #ytd9
SELECT Category,
ytdpaidamt,
(ytdpaidamt* 100.0)/(SELECT SUM(ytdpaidamt) FROM #ytd7)AS ytd_pct,
paidamt,
paidamt_pct
into #ytd9
FROM #ytd7
order by category
-- create consolidated table of categories, ytd, rounded pct for consumers and amt paid
drop table #ytd10
select y8.category, y8.ytdconsumers,
ROUND(CAST(y8.ytd_pct as MONEY),2) as ytd_cons_pct,
y8.consumers, y8.consumer_pct,y9.ytdpaidamt,
ROUND(CAST(y9.ytd_pct as MONEY),2) as _ytd_paid_pct,
y9.paidamt, y9.paidamt_pct
into #ytd10
from #ytd8 y8
inner join #ytd9 y9 on y8.category=y9.category
order by y8.category
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

PERFORMANCE OF SQL QUERY | 13 | 43 | |

In T-SQL how to handle [Expiration Date] = 'perpetual' or 'month-to-month' or 'dates vary' | 6 | 39 | |

Could you break this down for me? | 5 | 25 | |

SQL Server Running Balance | 14 | 32 |

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

Connect with top rated Experts

**7** Experts available now in Live!