drweber
asked on
Calculate ratio to total percentages in SQL Server 2000
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!
This gets the total count and applies to all the rows grouped by category. Didn't put in any checks for 0/null as you will not get any rows in a scenario where count from same table is null/0 in the first place so it would be a mute point.
I was trying the outer apply for performance reasons as I think that performs a little quicker, but basically this is my suggestion.
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
ASKER
Thanks for the quick reply
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
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
What are the contents of #ytd8? A sample of the data would be good enough unless the record count is 8 then please post all 8 records.
ASKER
I posted exactly what is in #YTD8, you've got all 8 records (the first "table" in the intial post)
It's a temp table, added up from several other tables
It's a temp table, added up from several other tables
ASKER
Anyone else out there have an idea on this?
From your sample data set, I assumed that you have a column as Count1 which holds the count for each category. Is category is unique in your table or do you have more than one row for same category?
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
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
ASKER
Sharath, using what you suggested
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.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)
ASKER
Sorry, forgot to answer, each category is already unique, done in a previous step
Check this:
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath, when I do that I still get back all ratios as 1.00
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
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
ANd here it is with my first example using OUTER APPLY. Also for the above, the GROUP BY is leftover code. It still works but is unnecessary since we are not using any aggregates in top portion.
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
ASKER
mwvisa - that works! now just need to round the results down to 2 DP - where does that go? I'm used to Oracle rounding
Sorry, in my previous query, I am dividing 61 with 61 only. mwvisa1's query should work for you. I changed my previous query. check this one:
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
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
SELECT a.Category
,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
,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
Here it is...
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 #ytd8ASKER
mwvisa - I'm getting errors when trying to use the code with the outer apply...
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?
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?
Did you check my query?
ASKER
Sharath, This is close but I need % - everything in the ratio column multiplied by 100
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
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
Simply multiply with 100.
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
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
ASKER
It's not rounding to 2 DP, even though you have the ROUND function in the command to calculate the percentage
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You both helped me get to where I wanted to go with this, although not directly. I ended up creating temp tables with the percentages for each group (I had multiple sets of these to do), then joining them together and rounding as part of the join/create combined table process.
ASKER
You both helped me get to where I wanted to go with this, although not directly. I ended up creating temp tables with the percentages for each group (I had multiple sets of these to do), then joining them together and rounding as part of the join/create combined table process.
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
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
, 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